UPSERT based on UNIQUE constraint with NULL values

Question

I have a Postgres table with a unique constraint on multiple columns, one of which can be NULL. I only every want to allow one record with NULL in that column for each combination.

create table my_table (
   col1 int generated by default as identity primary key,
   col2 int not null,
   col3 real,
   col4 int,
   constraint ux_my_table_unique unique (col2, col3)
);

I have an upsert query that I want to update col4 when it encounters a record with the same values in col2, col3:

insert into my_table (col2, col3, col4) values (p_col2, p_col3, p_col4)
on conflict (col2, col3) do update set col4=excluded.col4;

But the conflict is not firing when col3 is NULL. I have read about using triggers. What is the best solution to get the conflict to fire please?

Answer 1

If you can find a value that can never legally exist in col3 (make sure with a check constraint), you could use a unique index:

CREATE UNIQUE INDEX ON my_table (
   col2,
   coalesce(col3, -1.0)
);

and use that in your INSERT:

INSERT INTO my_table (col2, col3, col4)
VALUES (p_col2, p_col3, p_col4)
ON CONFLICT (col2, coalesce(col3, -1.0))
DO UPDATE SET col4 = excluded.col4;