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 * FROM 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 |

Answer 3

(copied from my other answer in a similar question)

It seems to be the case that for the OP col1 and col2 are independently unique. So that a combined unique constrained will NOT solve the insert/on conflict problem, as suggested in other answers.

One solution to this is a CTE which firstly only inserts a new row with col1 set and assigns a random value for col2 (avoiding a constraint violation on col2). And only then it updates col2 to its final value. Now this is what happens:

1. If col1 is duplicated, the first clause's conflict will hit and not insert a new row. Otherwise a new row is inserted with col1 set to its final value and col2 set to a temporary and unique value.
2. If col2 is duplicated, the second clause's conflict will hit and simply store the final value of :col2. Either for the existing row or for the newly created row from the insert clause.

Given that col1 and col2 are both strings/texts, we can use gen_random_uuid()::text to generate a unique string for the first insert clause:

with
    row as (
        insert into table(col1, col2)
        values(:col1, gen_random_uuid()::text)
        on conflict(col1) do set col1 = :col1
        returning col1
    )
update table
set col2 = :col2
from row
where row.col1 = :col1
returning col1, col2

with :col1 and :col2 being variables passed to the query.

Now if your column isn't a string/text you can come up with other unique values such as temporarily assigning -1 to col2, knowing that all your values are otherwise positive.

For when you have more than 2 independently unique columns, above approach scales in so far as you can move more (and even all) column updates into the CTE clause:

with
    row as (
        insert into table(col1, col2, ..., colN)
        values(
            :col1,
            gen_random_uuid()::text,
            ...
            gen_random_uuid()::text,
        )
        on conflict(col1) do set col1 = :col1
        returning col1
    ),
    col2_update as (
        update table
        set col2 = :col2
        from row
        where row.col1 = :col1
    ),
    ...
    colN_update as (
        update table
        set colN = :colN
        from row
        where row.col1 = :col1
    )
select col1, col2, ..., colN from row