Postgres conflict handling with multiple unique constraints

Question

I would like to use the insert.. on confict do update.. syntax with a table that has unique constraints on two columns. Is this possible?

e.g. mytable has separate unique constraints on col1 and col2.

I can write:

INSERT INTO mytable(col1, col2, col3) values ('A', 'B', 0) ON CONFLICT DO NOTHING; 

However this doesn't work:

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0)  ON CONFLICT  DO UPDATE SET col3 = EXCLUDED.col3 + 1; 

ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name

This also doesn't work:

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0) ON CONFLICT (col1, col2)  DO UPDATE SET col3 = EXCLUDED.col3 + 1; 

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

This syntax seems to be designed for a single composite unique constraint over two columns, rather than two constraints.

Is there any way to do a conditional update if either unique constraint is violated? This question How to upsert in Postgres on conflict on one of 2 columns? alludes to it but doesn't provide the syntax.

Answer 1

The ON CONFLICT clause needs a single unique constraint when we ask it to DO UPDATE. When a primary key is defined, it is sufficient to just reference the column name; which is the dominant example one tends to find.

You mention that you have 'separate unique constraints on col1 and col2', so I might assume your table definition is similar to this:

CREATE TABLE mytable(            col1 varchar UNIQUE,         col2 varchar UNIQUE,         col3 int ); 

But your query is referencing a composite constraint; rather than separate constraints. A modified table definition like this:

CREATE TABLE mytable2(       col1 varchar UNIQUE,     col2 varchar UNIQUE,     col3 int,     CONSTRAINT ux_col1_col2 UNIQUE (col1,col2) ); 

would work with your query above:

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0) ON CONFLICT (col1, col2)  DO UPDATE SET col3 = EXCLUDED.col3 + 1; 

You can reference this unique constraint as either ON CONFLICT (col1, col2) or as ON CONFLICT ON CONSTRAINT ux_col1_col2.

But wait, there's more...

The idea is to keep a counter column up to date which matches on either unique column, or insert zero if neither exists...

That's a different path than you're taking here. "matches on either unique column" allows for matching on both, either, or neither. If I understand your intent, just have a single label and increment the counters on the applicable records. So:

CREATE TABLE mytable2(       col1 varchar PRIMARY KEY,     col3 int ); INSERT INTO mytable2(col1,col3) SELECT incr_label,0 FROM (VALUES ('A'),('B'),('C')) as increment_list(incr_label) ON CONFLICT (col1) DO UPDATE SET col3 = mytable2.col3 + 1 RETURNING col1,col3; 

Answer 2

Because the conflict_target can't be two different unique constraints you have to use a simulated upsert and handle the conflicts yourself.

-- Desired

INSERT INTO mytable(col1, col2, col3) VALUES ('A', 'B', 0) ON CONFLICT DO UPDATE SET col3 = EXCLUDED.col3 + 1;

WITH upsert AS (   UPDATE mytable   SET col1 = 'A', col2 = 'B', col3 = col3 + 1   WHERE col1 = 'A' OR col2 = 'B'   RETURNING * ) INSERT INTO mytable (col1, col2, col3) SELECT 'A', 'B', 0 WHERE NOT EXISTS (SELECT * FORM upsert); 

This statement will result in rows that contain A or B or both, in other words uniqueness on col1 and uniqueness on col2 is satisfied.

Unfortunately this solution suffers from the limitation that there must be some logical link between A and B, otherwise if ('A', null) is inserted, followed by (null, B) and then by (A, B) you will end up with two rows, both incremented by the third insert:

| col1 | col2 | col3 | +------+------+------+ |    A | null |    1 | | null |    B |    1 |