2

I have the following SQL:

UPDATE table_name as t 
  set value = c.value 
from (values(10, 'key_1', 60),(11, 'key_2', 10)) as c(id, key, value) 
where t.id = c.id and t.key = c.key

This works fine only in the case if the given 'id' and 'key' already exist in the table. However I need to make it UPDATE the value if 'id' and 'key' exist in the DB and INSERT a new row with the given 'id', 'key' and 'value' if it does not.

I got this example working as an UPSERT but for one set of values only:

WITH upsert AS (
 UPDATE table_name 
   SET value=80 
 WHERE id=10 
   AND key='key_1' RETURNING *
) 
INSERT INTO table_name (id, key, value) 
SELECT 10, 'key_1', 10 
WHERE NOT EXISTS (SELECT * FROM upsert);

I'm not sure how to incorporate this into my case to work with multiple values (first example with UPDATE).

I do not have a unique constraint or index on (id, "key")

1 Answer 1

2

Use insert ... on conflict

insert into table_Name 
  (id, "key", value) 
values 
  (10, 'key_1', 60),
  (11, 'key_2', 10)
on conflict (id, "key") do update  
  set value = excluded.value;

This assumes that you have a unique key (or index) on table_name(id, "key")


Without a unique constraint or index you can do it like this:

WITH new_values (id, "key", value) as (
  values
  (10, 'key_1', 60),
  (11, 'key_2', 10)

), upsert as (
    update the_table t
        set value = c.value
    FROM new_values nv
    WHERE t.id = nv.id 
      AND t."key" = nv."key"
    RETURNING t.*
)
INSERT INTO the_table (id, "key", value)
SELECT nv.id, nv."key", nv.value
FROM new_values nv
WHERE NOT EXISTS (SELECT 1
                  FROM upsert up
                  WHERE up.id = nv.id
                    AND up."key" = nv."key")
;

The above is NOT safe for concurrent insert/updates - you can still wind up with duplicates. The only reliable way to avoid those duplicates is to use a unique constraint or index.

7
  • Thank you, but I do not have and can't have unique keys on those fields.
    – cycero
    Mar 3, 2020 at 14:03
  • 1
    @cycero: if you want to make sure you don't have duplicates you should have a unique index on those columns. There is no way you can ensure uniqueness without them - especially not in an environment with concurrent inserts/updates Mar 3, 2020 at 14:04
  • I don't actually need to make them unique, I want to INSERT when they don't exist and UPDATE the value if they do exist, even if there's more than 1 row affected.
    – cycero
    Mar 3, 2020 at 14:06
  • You are contradicting yourself. If you don't want to make them unique, you don't need the UPDATE or INSERT to begin with - just INSERT all the time. Your question clearly indicates that you do want to make them unique. Mar 3, 2020 at 14:07
  • 1
    @cycero: then remove the duplicates and create the unique index. I have added a solution that doesn't need such an index. But it is not safe for concurrent inserts. Mar 3, 2020 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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