clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2042737 fiddles created (16511 in the last week).

select version();
version
PostgreSQL 11.1 (Debian 11.1-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
 hidden batch(es)


CREATE TABLE test ( test_id serial NOT NULL PRIMARY KEY, x int NULL, y int NULL, CONSTRAINT x_y_unique UNIQUE (x,y), CONSTRAINT unique_with_wildcards_x_null EXCLUDE USING GIST ( (CASE WHEN x IS NULL THEN 0 ELSE 1 END) WITH <>, y with = ), CONSTRAINT unique_with_wildcards_x_one_null EXCLUDE USING GIST ( (CASE WHEN x IS NULL THEN 0 ELSE NULL END) WITH =, y with = ), CONSTRAINT unique_with_wildcards_y_null EXCLUDE USING GIST ( x with =, (CASE WHEN y IS NULL THEN 0 ELSE 1 END) WITH <> ), CONSTRAINT unique_with_wildcards_y_one_null EXCLUDE USING GIST ( x with =, (CASE WHEN y IS NULL THEN 0 ELSE NULL END) WITH = ) ) ;
 hidden batch(es)


insert into test (x, y) values (1, 2), (2, null), (null, 3) ;
3 rows affected
 hidden batch(es)


select * from test ;
test_id x y
1 1 2
2 2
3 3
 hidden batch(es)


insert into test (x, y) values (1, 2) ;
ERROR: duplicate key value violates unique constraint "x_y_unique" DETAIL: Key (x, y)=(1, 2) already exists.
 hidden batch(es)


insert into test (x, y) values (1, null) ;
ERROR: conflicting key value violates exclusion constraint "unique_with_wildcards_y_null" DETAIL: Key (x, ( CASE WHEN y IS NULL THEN 0 ELSE 1 END))=(1, 0) conflicts with existing key (x, ( CASE WHEN y IS NULL THEN 0 ELSE 1 END))=(1, 1).
 hidden batch(es)


insert into test (x, y) values (null, 2) ;
ERROR: conflicting key value violates exclusion constraint "unique_with_wildcards_x_null" DETAIL: Key (( CASE WHEN x IS NULL THEN 0 ELSE 1 END), y)=(0, 2) conflicts with existing key (( CASE WHEN x IS NULL THEN 0 ELSE 1 END), y)=(1, 2).
 hidden batch(es)


insert into test (x, y) values (2, null) ;
ERROR: conflicting key value violates exclusion constraint "unique_with_wildcards_y_one_null" DETAIL: Key (x, ( CASE WHEN y IS NULL THEN 0 ELSE NULL::integer END))=(2, 0) conflicts with existing key (x, ( CASE WHEN y IS NULL THEN 0 ELSE NULL::integer END))=(2, 0).
 hidden batch(es)


insert into test (x, y) values (null, 3) ;
ERROR: conflicting key value violates exclusion constraint "unique_with_wildcards_x_one_null" DETAIL: Key (( CASE WHEN x IS NULL THEN 0 ELSE NULL::integer END), y)=(0, 3) conflicts with existing key (( CASE WHEN x IS NULL THEN 0 ELSE NULL::integer END), y)=(0, 3).
 hidden batch(es)


insert into test (x, y) values (null, null) ;
1 rows affected
 hidden batch(es)


select * from test ;
test_id x y
1 1 2
2 2
3 3
9
 hidden batch(es)