1

I have a table with columns 'id', 'a', 'b, 'c', 'd'. Also, I have a unique constraint on ('a', 'b', 'c', 'd') so no entries can be added where all those fields are the same. I want to upsert entries e.g. I have in db:

1 a_value b_value c_value d_value

and I'm upserting

   INSERT INTO MYTABLE(a, b, c, d) values ('a_value', 'b_value', 'c_value', 'd_value__new') on conflict (a, b, c, d) DO UPDATE SET d= excluded.d;

But this creates new entry when I expected that it will update d_value to d_value_new on existing entry. What am I doing wrong?

See example here:

https://dbfiddle.uk/nL7fRpt_

5
  • 1
    Your sample INSERT is equivalent to DO NOTHING, but your later commend contradicts that; care to create a repro at dbfiddle.uk?
    – mustaccio
    Commented Jan 16 at 14:20
  • @mustaccio dbfiddle.uk/nL7fRpt_ I would have expected row to be updated, not new one to be inserted. What I am doing wrong?
    – kichma
    Commented Jan 16 at 14:37
  • 1
    Hi, and welcome to dba.se! You have on conflict (a, b, c, d)... - but there is no conflict - d_value != d_value_new, so therefore you generate a new INSERT!
    – Vérace
    Commented Jan 16 at 15:02
  • @Vérace Ok, I think I'm starting to understand, but how to achieve what I want? So that there cannot be two entries with same values for a,b,c,d. And if I insert value with existing a,b,c and different d it should update d with new value.
    – kichma
    Commented Jan 16 at 15:06
  • If you set a unique constraint on (a,b) then unicity applies to the whole (a,b) tuple. In other words, the individual columns are not unique, only the combination is unique. So it is possible to have both (1,1) and (1,2), since they are different tuples even though the value of a is the same. So it is not clear whether you want this, or each column to be unique.
    – bobflux
    Commented Jan 17 at 16:56

1 Answer 1

1

From what I've read in the comments, you want to achieve the following behaviour: when doing an INSERT if values for columns (a, b, c) already exist in a row, then to update the d column of that row to the new value. If that's the case, then you need to put the unique constraint on (a, b, c) NOT d, since with d it won't be triggered since you insert new value for d). Also change the ON CONFLICT statement to just check (a,b, c). Note: with this constraint you will not be able to have more than 1 row that has the same values for (a, b, c). Here is a fiddle with those changes, check it out https://dbfiddle.uk/JsLZgGZW . In the future make sure you provide more accurate information of what you want to achieve so you can get a precise answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.