Postgres conflict handling with multiple unique constraints

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;