How to find child tables that inherit from another table in PSQL
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 parentFROM 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 parentFROM 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.relnamespaceJOIN pg_namespace cn ON cn.oid = c.relnamespaceWHERE 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 relnameFROM pg_class,pg_inheritsWHERE pg_class.oid=pg_inherits.inhrelidAND inhparentIN (SELECT oid FROM pg_class WHERE relname='your_master_partition')ORDER BY relname;