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?.
-- example with a composite foreign key
drop table if exists master cascade;
create table master(
id1 int,
id2 int,
primary key(id1, id2));
drop table if exists detail;
create table detail(
id int primary key,
rid1 int,
rid2 int,
foreign key (rid1, rid2) references master);
DROP TABLE
CREATE TABLE
DROP TABLE
CREATE TABLE
select
cb.relname as base_table,
cb.relowner::regrole as base_owner,
(select string_agg(attname, ',')
from pg_attribute
where attrelid = confrelid and attnum = any(confkey)
) as base_columns,
cr.relname as referencing_table,
cr.relowner::regrole as reftable_owner,
(select string_agg(attname, ',')
from pg_attribute
where attrelid = conrelid and attnum = any(conkey)
) as ref_columns
from pg_constraint c
join pg_class cb on cb.oid = c.confrelid
join pg_class cr on cr.oid = c.conrelid
where c.confrelid = 'public.master'::regclass
| base_table | base_owner | base_columns | referencing_table | reftable_owner | ref_columns |
|---|---|---|---|---|---|
| master | u_286591339 | id1,id2 | detail | u_286591339 | rid1,rid2 |
SELECT 1