Postgres 14

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 empl (
  eid int not null primary key
, ename text not null
, dept_id int not null
);

create table dept (
  did int not null primary key
, dname text not null
, manager_id int not null
);


alter table dept
add foreign key (manager_id)
references empl(eid);

alter table empl
add foreign key (dept_id)
references dept(did);
insert into dept values (1, 'IT', 101);
ERROR:  insert or update on table "dept" violates foreign key constraint "dept_manager_id_fkey"
DETAIL:  Key (manager_id)=(101) is not present in table "empl".


insert into empl values (101, 'Tom', 1);
ERROR:  insert or update on table "empl" violates foreign key constraint "empl_dept_id_fkey"
DETAIL:  Key (dept_id)=(1) is not present in table "dept".