22

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.

Watusimoto
  • 1,773
  • 1
  • 23
  • 38

3 Answers3

44

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);
Michael Krelin - hacker
  • 138,757
  • 24
  • 193
  • 173
  • When I wrote "sql query", I meant something I could pass to PSQL on the command line, as opposed to something like \d that must be run interactively. So selecting from pg_inherits is a good start. Where can I find a table listing table names and their oids? – Watusimoto Sep 22 '09 at 18:38
  • Das is perfekt! Vielen dank! – Watusimoto Sep 22 '09 at 18:49
  • 2
    Watusimoto - \d can be passed to psql on the command line as well :) –  Sep 22 '09 at 19:06
  • 1
    `\d+ _parentTable_` will append a list all child tables from the command-line console – Yzmir Ramirez May 10 '16 at 00:04
11

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'
Jeroen
  • 317
  • 4
  • 8
3

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;
user3612491
  • 223
  • 3
  • 7