SQL UPSERT in Postgres

Question

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

Answer 1

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.