Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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


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 =
        )
) ;
insert into test
  (x, y)
values
  (1, 2),
  (2, null),
  (null, 3)  ;
3 rows affected

select * from test ;


test_id
x
y


1
1
2

2
2
null

3
null
3


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.


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).


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).


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).


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).


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

select * from test ;


test_id
x
y


1
1
2

2
2
null

3
null
3

9
null
null
test_id	x	y
1	1	2
2	2	null
3	null	3
9	null	null