Check if table inherits from other table in PostgreSQL

Question

In PostgreSQL for these tables

CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- in feet
);

CREATE TABLE cities_capitals (
    state           char(2)
) INHERITS (cities);

How can I programmatically check whether one of these tables inherits from another table or not? (Think information_schema, pg_catalog, ...)

Should be true for cities_capitals and false for cities.

Erwin Brandstetter · Accepted Answer · 2022-12-19T05:43:31.730

17

There is a catalog table for that: pg_inherits.

The catalog pg_inherits records information about table inheritance hierarchies. There is one entry for each direct child table in the database. (Indirect inheritance can be determined by following chains of entries.)

Here's a query that fits your question:

SELECT EXISTS (
   SELECT FROM pg_catalog.pg_inherits
   WHERE  inhrelid = 'public.cities_capitals'::regclass
   );

TRUE if table cities_capitals inherits from somewhere, else FALSE.
Schema-qualify the name to be sure.

edited Dec 19 '22 at 05:43

answered Oct 07 '11 at 16:58

Erwin Brandstetter

605,456
145
1,078
1,228

How should I correlate the inhrelid with a table name in a schema? – Axel Fontaine Oct 07 '11 at 17:01
@AxelFontaine: I added to my answer. It's actually quite simple - once you know it. ;) – Erwin Brandstetter Oct 07 '11 at 17:06

score 4 · Answer 2 · answered Oct 07 '11 at 17:01

The following statement will retrieve the tables that cities inherits from. If the table does not inherit from another table, the result will be empty:

select bt.relname as table_name, bns.nspname as table_schema 
from pg_class ct 
    join pg_namespace cns on ct.relnamespace = cns.oid and cns.nspname = 'public' 
    join pg_inherits i on i.inhrelid = ct.oid and ct.relname = 'cities ' 
    join pg_class bt on i.inhparent = bt.oid 
    join pg_namespace bns on bt.relnamespace = bns.oid

score 0 · Answer 3 · answered Aug 24 '14 at 19:12

From Postgresql AutoDoc I found this SQL:

SELECT parnsp.nspname AS par_schemaname
    , parcla.relname AS par_tablename
    , chlnsp.nspname AS chl_schemaname
    , chlcla.relname AS chl_tablename
 FROM pg_catalog.pg_inherits
 JOIN pg_catalog.pg_class AS chlcla ON (chlcla.oid = inhrelid)
 JOIN pg_catalog.pg_namespace AS chlnsp ON (chlnsp.oid = chlcla.relnamespace)
 JOIN pg_catalog.pg_class AS parcla ON (parcla.oid = inhparent)
 JOIN pg_catalog.pg_namespace AS parnsp ON (parnsp.oid = parcla.relnamespace)

This is helpful as you can test in both directions with one query.

score 0 · Answer 4 · answered Dec 19 '22 at 05:54

0

Another way is by using INHERITS

SELECT INHERITS(
  (SELECT oid FROM pg_class WHERE relname = 'cities_capitals'),
  (SELECT oid FROM pg_class WHERE relname = 'cities')
)

This will return t if cities_capitals inherits from cities and f if not

answered Dec 19 '22 at 05:54

Abdulla Nilam

36,589
17
64
85

Check if table inherits from other table in PostgreSQL

4 Answers4

Linked