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.relnamespace::regnamespace as base_schema,
(select string_agg(attname, ',')
from pg_attribute
where attrelid = confrelid and attnum = any(confkey)
) as base_columns,
cr.relname as referencing_table,
cr.relnamespace::regnamespace as reftable_schema,
(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
-- sample schema condition
where cb.relnamespace = 'public'::regnamespace
| base_table | base_schema | base_columns | referencing_table | reftable_schema | ref_columns |
|---|---|---|---|---|---|
| master | public | id1,id2 | detail | public | rid1,rid2 |
SELECT 1