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

CREATE TABLE test(
   a int
 , b int
 , c int
 , CHECK (NOT((a,b,c) IS NULL))
);

-- These all work!
INSERT INTO test (a, b, c) 
VALUES
  (1   , NULL, NULL)
, (NULL, 2   , NULL)
, (NULL, NULL, 3   )
, (NULL, 2   , 3   )
, (1   , NULL, 3   )
, (1   , 2   , NULL)
, (1   , 2   , 3   );

CREATE TABLE



INSERT 0 7


-- This also works:
INSERT INTO test VALUES (11, NULL, NULL);

INSERT 0 1


TABLE test;


a
b
c


1
null
null

null
2
null

null
null
3

null
2
3

1
null
3

1
2
null

1
2
3

11
null
null



SELECT 8


-- But this one doesn't (as per requirement):
INSERT INTO test VALUES (NULL, NULL, NULL);

ERROR:  new row for relation "test" violates check constraint "test_check"
DETAIL:  Failing row contains (null, null, null).

a	b	c
1	null	null
null	2	null
null	null	3
null	2	3
1	null	3
1	2	null
1	2	3
11	null	null