Postgres 13

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 if not exists foo (
  x    int
, y    int
, z    bool                                         -- NOT NULL ???
, data text                                         -- my addition
, CONSTRAINT x_or_y CHECK (z       AND x IS NOT NULL
                        OR z=FALSE AND y IS NOT NULL)
);

CREATE UNIQUE INDEX ux ON foo (x) WHERE z;          -- switched!!!
CREATE UNIQUE INDEX uy ON foo (y) WHERE z=FALSE;

CREATE OR REPLACE VIEW  foo_view AS 
SELECT * FROM foo;
  
INSERT INTO foo_view
       (x   , y   , z    , data )
VALUES (5   , null, true , 'foo')
     , (null, 5   , false, 'bar')
RETURNING *;


x
y
z
data


5
null
t
foo

null
5
f
bar


-- INSERT to table does not raise error:
INSERT INTO foo
       (x   , y   , z   , data     )
VALUES (5   , null, true, 'foo_new1')
ON     CONFLICT (x) WHERE z
DO     UPDATE
SET    data = EXCLUDED.data
RETURNING *;


x
y
z
data


5
null
t
foo_new1


-- INSERT to view raises error (!!!):
INSERT INTO foo_view
       (x   , y   , z   , data     )
VALUES (5   , null, true, 'foo_new2')
ON     CONFLICT (x) WHERE z
DO     UPDATE
SET    data = EXCLUDED.data
RETURNING *;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification