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 |