Menu
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, col3));
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.
836
PostgreSQL lets you either add or modify a record within a table depending on whether the record already exists. This is commonly known as an "upsert" operation (a portmanteau of "insert" and "update").
In relational databases, the term upsert is referred to as merge. The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, it will insert the new row. That is why we call the action is upsert (the combination of update or insert).
ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action.
The INSERT ON CONFLICT statement allows you to update an existing row that contains a primary key when you execute the INSERT statement to insert a new row that contains the same primary key. This feature is also known as UPSERT or INSERT OVERWRITE. It is similar to the REPLACE INTO statement of MySQL.
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 table (col1, col2, col3)
VALUES ('1', 'A', 'colval1A')
ON CONFLICT (col1, col2) DO UPDATE
SET col3 = 'good_value'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With