Postgres 15

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(
  userid int, 
  isactive boolean,
  misc text);

create unique index my_partial_unique  
     on test (userid) 
     where isactive is true;
insert into test values 
(1, false, 'a'),
(1, false, 'b'),
(1, true,  'c')
returning *;

CREATE TABLE



CREATE INDEX




userid
isactive
misc


1
f
a

1
f
b

1
t
c



INSERT 0 3


insert into test values 
(1, false, 'd');

INSERT 0 1


insert into test values 
(2, false, '2a'),
(2, true,  '2b');

INSERT 0 2


insert into test values 
(1, true, 'e');

ERROR:  duplicate key value violates unique constraint "my_partial_unique"
DETAIL:  Key (userid)=(1) already exists.


update test 
  set isactive=true 
  where userid=1 and misc='b';

ERROR:  duplicate key value violates unique constraint "my_partial_unique"
DETAIL:  Key (userid)=(1) already exists.