Table inheritance

Back to Catalog
Goal Find inheritance between base tables. Use table inheritance carefully because, for instance, certain constraints are not inherited and must be redefined on child tables.
Notes The query outputs information as to whether the child is partition or not because internally PostgreSQL uses the inheritance mechanism in case of declarative partitioning.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source system catalog only
SQL Query 
SELECT pn.nspname AS parent_schema, p.relname AS parent_table, pc.nspname AS child_schema,  c.relname AS child_table,
c.relispartition AS child_is_partition
FROM pg_inherits pi INNER JOIN pg_class p ON pi.inhparent=p.oid
INNER JOIN pg_namespace pn ON p.relnamespace=pn.oid
INNER JOIN pg_authid AS ap ON pn.nspowner=ap.oid
INNER JOIN pg_class c ON pi.inhrelid=c.oid
INNER JOIN pg_namespace pc ON c.relnamespace=pc.oid
INNER JOIN pg_authid AS ac ON pc.nspowner=ac.oid
WHERE (pn.nspname='public' OR ap.rolname<>'postgres') 
AND (pc.nspname='public' OR ac.rolname<>'postgres')
ORDER BY pn.nspname, p.relname, pc.nspname, c.relname;
Collections

This query belongs to the following collections:

NameDescription
Find problems about base tablesA selection of queries that return information about the data types, field sizes, default values as well as general structure of base tables. Contains all the types of queries - problem detection, software measure, and general overview
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories

This query is classified under the following categories:

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Table inheritanceQueries of this category provide information about the inheritance between base tables.