Postgres 10

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 a (id int primary key);

create table b (id int primary key, aid int);

alter table b
  add constraint fkb foreign key (aid) references a(id) match full;
insert into a values (1),(2),(3);
insert into b values (1, 1),(2, 1),(3, 1);
3 rows affected

3 rows affected

delete from a where id = 1;
ERROR:  update or delete on table "a" violates foreign key constraint "fkb" on table "b"
DETAIL:  Key (id)=(1) is still referenced from table "b".


delete from b where aid = 1;
3 rows affected

delete from a where id = 1;
1 rows affected