[ACCEPTED]-How to find child tables that inherit from another table in PSQL-postgresql

Accepted answer
Score: 43

What do you mean "from sql query"? Does 2 it mean SELECTing from pg_inherits is not good enough for 1 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);
Score: 9

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'

0

Score: 2

If you want to find all child's from the 2 master partition you can simply execute 1 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;

More Related questions