The list of all the queries

Columns defined in a subtable

Query goal: Find columns that have been added to a subtable, i.e., these were not defined in its immediate supertable.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH inheritance AS (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'))

SELECT i.parent_schema, i.parent_table, c.table_schema, t.table_type, c.table_name , c.column_name, 
CASE WHEN data_type ILIKE 'character%' AND character_maximum_length IS NOT NULL THEN data_type || '(' || character_maximum_length::text || ')'
WHEN data_type ILIKE 'timestamp%' AND datetime_precision IS NOT NULL THEN data_type || '(' || datetime_precision || ')'
WHEN data_type ILIKE 'numeric%' AND numeric_precision IS NOT NULL THEN data_type || '(' || numeric_precision::text || ',' ||coalesce(numeric_scale,0)::text || ')'
WHEN data_type ILIKE 'interval%' AND interval_type IS NOT NULL THEN data_type || '(' || interval_type::text || ')'
WHEN data_type='USER-DEFINED' THEN udt_schema || '.' || udt_name 
ELSE data_type END AS data_type
FROM information_schema.columns c INNER JOIN information_schema.schemata s ON c.table_schema=s.schema_name
INNER JOIN information_schema.tables t USING (table_schema, table_name)
INNER JOIN inheritance AS i ON i.child_schema=c.table_schema AND i.child_table=c.table_name
WHERE (c.table_schema = 'public'
OR s.schema_owner<>'postgres')

AND NOT EXISTS (SELECT *
FROM inheritance AS i INNER JOIN INFORMATION_SCHEMA.columns AS ic ON i.parent_schema=ic.table_schema AND i.parent_table=ic.table_name
WHERE c.table_schema=i.child_schema 
AND c.table_name=i.child_table
AND c.column_name=ic.column_name
)
ORDER BY parent_table, parent_schema, table_schema, table_name, column_name;

Collections where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
Table inheritanceQueries of this category provide information about the inheritance between base tables.

The list of all the queries