Goal Find partitioned tables that have been implemented by using the declarative approach. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10.
Notes The query also finds partitions that are intended to have sub-partitions. These are presented as partitioned tables of their own.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH partitioned AS (SELECT 
n.nspname AS table_schema,
c.oid AS table_oid,
c.relname AS table_name,
CASE WHEN pt.partstrat='h' THEN 'hash partitioned table'
WHEN pt.partstrat='l' THEN 'list partitioned table'
WHEN pt.partstrat='r' THEN 'range partitioned table' END AS partition_strategy,
CASE WHEN pt.partdefid=0 THEN FALSE
ELSE TRUE END AS has_default_partition,
partattrs,
relispartition
FROM pg_class AS c INNER JOIN pg_partitioned_table AS pt ON pt.partrelid=c.oid
INNER JOIN pg_namespace AS n ON c.relnamespace=n.oid
WHERE n.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
partitioned_unnest AS (SELECT table_schema, table_name, partition_strategy, has_default_partition, relispartition, table_oid, partattrs, target_col_num, ordin
FROM partitioned, unnest(partitioned.partattrs) WITH ORDINALITY AS f(target_col_num, ordin)),
partitioned_with_names AS (SELECT table_schema, table_name, partition_strategy, has_default_partition, relispartition, table_oid, string_agg(a_target.attname, ', ' ORDER BY a_target.attname) AS  partition_key_columns
FROM partitioned_unnest AS pu INNER JOIN pg_attribute a_target ON pu.target_col_num = a_target.attnum AND pu.table_oid = a_target.attrelid AND a_target.attisdropped = false
GROUP BY table_schema, table_name, partition_strategy, has_default_partition, relispartition, table_oid)
SELECT table_schema, table_name, partition_strategy, has_default_partition, partition_key_columns, relispartition, string_agg(d.nspname || '.' || d.relname, ', ' ORDER BY d.nspname, d.relname) AS partitions
FROM partitioned_with_names pt LEFT JOIN (SELECT nspname, relname, refobjid 
FROM pg_depend INNER JOIN pg_class AS partitions ON pg_depend.objid=partitions.oid
INNER JOIN pg_namespace AS n ON partitions.relnamespace=n.oid
WHERE pg_depend.deptype='a'
AND partitions.relispartition=TRUE) AS d ON pt.table_oid=d.refobjid
GROUP BY table_schema, table_name, partition_strategy, has_default_partition, partition_key_columns, relispartition
HAVING Count(d.relname)=1
ORDER BY table_schema, table_name;

Collections

This query belongs to the following collections:

NameDescription
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 data managementQueries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient.
PerformanceQueries of this category provide information about indexes in a database.

Further reading and related materials:

Reference
https://www.postgresql.org/docs/current/ddl-partitioning.html