Search code examples
postgresqlinsertsql-updateupsert

Upsert if on conflict occurs on multiple columns in Postgres db

I have a table in Postgres db with no primary keys. I want to update if the combination of 2 columns have the same value.

...
ON CONFLICT (col1, col2)
DO UPDATE

ELSE 
INSERT
...

I couldn't find anything without primary keys. Also, the combination of col1 and col2 is unique. There could be multiple rows for col1 with the same value or with col2 but together cannot.

So my table is something like this:

col1  col2
1     A    
1     B
2     A
2     B

I cannot have a unique constraint on either of these columns but adding the index together in a combination works as follows:

CREATE TABLE example (
col1 integer,
col2 integer,
col3 integer,
UNIQUE (col1, col2));

But now, how to handle the inserts. What should be the ON CONFLICT condition as we cannot have on 2 columns so back to the same issue.

Solution

  • Actually, found it here but not in the post marked as answer but the most rated post. Use multiple conflict_target in ON CONFLICT clause

    So our query will be as follows:

    INSERT into example (col1, col2, col3)
VALUES (1, 2, 3)
ON CONFLICT (col1, col2) DO UPDATE 
SET col3 = 42