ON CONFLICT on two columns where one can be null

Question

I'm trying to use ON CONFLICT on two columns where one can be null. Unfortunatelly with partial index I don't seem to be able to do it.

create table tbl( col1 int, col2 int, col3 boolean);

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NOT NULL; -- NOT NULL

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NULL;     -- NULL

INSERT INTO tbl(col1, col2)
values(1, 3) 
ON CONFLICT(col1, col2) DO UPDATE SET col3 = true;

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

CREATE UNIQUE INDEX ON tbl (col1, col2) WHERE col2 IS NULL; doesn't make sense. — ypercubeᵀᴹ, Jun 01 '17 at 18:29
@ypercubeᵀᴹ That's a good observation. That really doesn't make sense. — Evan Carroll, Jun 02 '17 at 07:19
Related: https://dba.stackexchange.com/questions/151431/postgresql-upsert-issue-with-null-values — Ciro Santilli OurBigBook.com, Jan 13 '23 at 05:00

score 4 · Accepted Answer · answered Jun 01 '17 at 17:32

Because PostgreSQL can not infer it from the values, you need the index_predicate

index_predicate Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred. Follows CREATE INDEX format. SELECT privilege on any column appearing within index_predicate is required.

Sample Data

CREATE TABLE tbl( col1 int, col2 int, col3 boolean);

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NOT NULL; -- NOT NULL

CREATE UNIQUE INDEX ON tbl (col1, col2)
WHERE col2 IS NULL;     -- NULL

INSERT INTO tbl(col1, col2)
VALUES (1, 3, false); -- set value to false

Test

INSERT INTO tbl(col1, col2)
VALUES (1, 3)
ON CONFLICT(col1, col2)
WHERE col2 IS NOT NULL  -- predicate here
DO UPDATE SET col3 = true;

TABLE tbl;

 col1 | col2 | col3 
------+------+------
    1 |    3 | t
(1 row)

ON CONFLICT on two columns where one can be null

1 Answers1

Sample Data

Test