Menu
I have two unique constraints on the same table, and I want to do an upsert statement on that table.
Is it possible to specify the two conflicts in the upsert? I saw this: How to upsert in Postgres on conflict on one of 2 columns?
but my issue is slightly more involved because one of the unique constraints is a subset of the other unique constraint. I.e.
unique_constraint_1 = (col_1) unqiue_constraint_2 = (col_1, col_2)
INSERT INTO table (col_1, col_2, col_3)
VALUES (val_1, val_2, val_3)
ON CONFLICT (what do I put here to account for both constraints?)
DO NOTHING;
thanks!
865
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 PostgreSQL, the UPSERT operation means either UPDATE or INSERT operation. The UPSERT operation allows us to either insert a row or skip the insert operation if a row already exists and update that row instead. Suppose you want to insert bulk data from one table to another table that already has some data.
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.
ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action.
According to documentation, ON CONFLICT covers all unique constraints by default.
when omitted, conflicts with all usable constraints (and unique indexes) are handled
In your case there is no need for two constraints, as Grzegorz Grabek pointed out already. That is because the stricter single-column constraint already covers the looser two-column constraint.
113
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
