Postgres conflict handling with multiple unique constraints
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.
Solution 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;
Solution 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 |