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.

Answer 1

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 1
   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.