Menu
In PSQL, is there a good way of finding all the tables that inherit from another table? Ideally, I could get the data from a SQL query, but at this point, I'd be happy with any reliable method.
847
It is a process for deriving one object from another object so that they have shared properties. Inheritance in PostgreSQL allows you to create a child table based on another table, and the child table will include all of the columns in the parent table.
Child tables and parent tables are just normal database tables, but they're linked in a way that's described by a parent–child relationship. It's usually used to specify where one table's value refers to the value in another table (usually a primary key of another table).
col = tbl1. col); If you don't know which table has surplus rows or both have, you can either repeat the above query after switching table names, or: SELECT * FROM tbl1 FULL OUTER JOIN tbl2 USING (col) WHERE tbl2 col IS NULL OR tbl1.
What do you mean "from sql query"? Does it mean SELECTing from pg_inherits is not good enough for you?
SELECT pg_inherits.*, c.relname AS child, p.relname AS parent
FROM
pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid)
JOIN pg_class as p ON (inhparent=p.oid);
If you also need the schema names:
SELECT cn.nspname AS schema_child, c.relname AS child, pn.nspname AS schema_parent, p.relname AS parent
FROM pg_inherits
JOIN pg_class AS c ON (inhrelid=c.oid)
JOIN pg_class as p ON (inhparent=p.oid)
JOIN pg_namespace pn ON pn.oid = p.relnamespace
JOIN pg_namespace cn ON cn.oid = c.relnamespace
WHERE p.relname = 'your table name' and pn.nspname = 'your schema name'
If you want to find all child's from the master partition you can simply execute it like:
SELECT relname
FROM pg_class,pg_inherits
WHERE pg_class.oid=pg_inherits.inhrelid
AND inhparent
IN (SELECT oid FROM pg_class WHERE relname='your_master_partition')
ORDER BY relname;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With