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;