Axel Fontaine Axel Fontaine - 2 years ago 155
SQL Question

Check if table inherits from other table in PostgreSQL

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download