Postgres 9.6

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 foo
    (fk, int) 
AS VALUES
    ( 1, -12 ),
    ( 1,  20 ),
    ( 1,   0 );
3 rows affected

CREATE UNIQUE INDEX IndexName ON foo (fk) WHERE (int>0);
INSERT INTO foo(fk,int) values(1,99);
ERROR:  duplicate key value violates unique constraint "indexname"
DETAIL:  Key (fk)=(1) already exists.


INSERT INTO foo(fk,int) values(1,-99);
1 rows affected