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 orders (
   id smallserial,
   user_id int,
   status text check (status in ('pending','in_process','completed','declined') ),
   exclude (user_id with =) where (status in ('pending','in_process'))
);

CREATE TABLE


insert into orders (user_id,status) values
(1,'pending'),
(2,'in_process'),
(3,'completed'),
(4,'declined');

INSERT 0 4


insert into orders (user_id,status) values
(1,'pending');

ERROR:  conflicting key value violates exclusion constraint "orders_user_id_excl"
DETAIL:  Key (user_id)=(1) conflicts with existing key (user_id)=(1).


insert into orders (user_id,status) values
(1,'in_process');

ERROR:  conflicting key value violates exclusion constraint "orders_user_id_excl"
DETAIL:  Key (user_id)=(1) conflicts with existing key (user_id)=(1).