Unique index on expressions not recognized for UPSERT

Question

I did this to solve the problem regarding having null values in 2 columns for a unique constraint in PostgreSQL:

CREATE UNIQUE INDEX uc_column1_column2_column3_column4 ON schema.table
(column1, column2, COALESCE(column3, '0'), COALESCE(column4,'0'));

See here: Create unique constraint with null columns

But now I have an other problem because using index instead of constraint I have the error:

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

when I do this SQL request:

INSERT INTO table
        (column1,column2, column3, column4, columnA, columnB, columnC) 
        VALUES (1,2,3,4,A,B,C)
ON CONFLICT (column1,column2, column3, column4) 
DO UPDATE SET column1= EXCLUDED.column1
RETURNING *

Note: I do get the correct constraint error without the "on conflict" line.

On conflict (column1, column2, COALESCE(column3, '0'), COALESCE(column4,'0')) — Cetin Basoz, Aug 06 '21 at 14:32
Be so kind as to start with your core table definition `CREATE TABLE ...` and your version of Postgres. And about `DO UPDATE SET column1= EXCLUDED.column1` .. consider https://stackoverflow.com/a/42217872/939860 — Erwin Brandstetter, Aug 06 '21 at 15:06
Thanks you Cetin. It works as expected this way. Thanks Erwin. I will have a deeper look to avoid `DO UPDATE SET column1=EXCLUDED.column1` . On my first try `DO NOTHING` returns nothing... — Etienne Savary, Aug 06 '21 at 16:34

score 0 · Answer 1 · answered Aug 06 '21 at 15:36

If the expression index with COALESCE(column3, '0') does not lead to conflicts between NULL and 0, you might as well define column3 as column3 integer NOT NULL DEFAULT 0(guessing the data type) to begin with. Same for column4. That would remove all complications.

While you (have to) work with that expression index, your "conflict target" has to match the definition of the unique index, like Cetin suggested:

INSERT INTO table
       (column1, column2, column3, column4, columnA, columnB, columnC) 
VALUES (1, 2, 3, 4, A, B, C)
ON     CONFLICT (column1, column2, COALESCE(column3, '0'), COALESCE(column4,'0')) DO UPDATE
SET    column1 = EXCLUDED.column1
RETURNING *;

Since you are working with a UNIQUE INDEX and not a UNIQUE CONSTRAINT, you cannot just supply the constraint name instead and must go the route of "unique index inference". Or, as the manual puts it:

conflict_target can perform unique index inference. When performing inference, it consists of one or more index_column_name columns and/or index_expression expressions, and an optional index_predicate. All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes.

Aside:

DO UPDATE SET column1= EXCLUDED.column1 never changes anything by definition. It's still costly as it writes a new row version. Probably in use to always return a row. Typically, there are cheaper solutions. See:

How to use RETURNING with ON CONFLICT in PostgreSQL?

Unique index on expressions not recognized for UPSERT

1 Answers1