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