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 

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")


Use insert ... on conflict

insert into table_Name 
  (id, "key", value) 
  (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 (
  (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"
INSERT INTO the_table (id, "key", value)
SELECT nv.id, nv."key", nv.value
FROM new_values nv
                  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.

| improve this answer | |
  • Thank you, but I do not have and can't have unique keys on those fields. – cycero Mar 3 at 14:03
  • @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 – a_horse_with_no_name Mar 3 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 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. – a_horse_with_no_name Mar 3 at 14:07
  • So, the table contains duplicate entries because of an error which has been in the code for a while. What I need to do is INSERT if no match and UPDATE when there's a match, that's it. I do not need to only INSERT and grow the table for no reason. – cycero Mar 3 at 14:10

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.