17

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!

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
chris
  • 1,869
  • 4
  • 29
  • 52
  • 2
    No, unfortunately you can't. You can specify a *conflict* https://www.postgresql.org/docs/9.5/static/sql-insert.html, but only one. – wildplasser Jun 29 '16 at 21:38
  • 4
    It's been 1 year since this was asked. I have the same issue. An `UPSERT` fails because the conflict is on the `UNIQUE` constraint that is not accounted for by the single `ON CONFLICT`. Has anything changed? Any workarounds? – Thalis K. Aug 27 '17 at 15:31
  • 3
    Wouldn't the 2nd constraint cover the first? – Int'l Man Of Coding Mystery Mar 16 '20 at 12:24
  • 6
    First of all if col1 is unique then col1, col2 is always unique in same table. You don't need two unique cons. Just remove second cons. It will resolves your problem and speed up all inserts into that table. – Grzegorz Grabek May 23 '20 at 13:19
  • @GrzegorzGrabek can you explain your argument? Say I have a `UserTable` and constraints: * unique(first_name, last_name) * unique(city, street,number,last_name) * unique(id) `on conflict ( first_name, last_name), (city, street,number,last_name) )` would not mean the same as `on conflict ( first_name,city, street,number,last_name) )` as you suggested when I got that right. Note: unique(id) is not included in the `on conflict` clause. – claus Mar 16 '23 at 10:58

1 Answers1

2

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.

Jonathan Jacobson
  • 1,441
  • 11
  • 20