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
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, 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, 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, table_oid)
SELECT table_schema, table_name, partition_strategy, has_default_partition, partition_key_columns, string_agg(n.nspname || '.' || partitions.relname, ', ' ORDER BY n.nspname, partitions.relname) AS partition
FROM partitioned_with_names pt INNER JOIN pg_depend d ON pt.table_oid=d.refobjid
INNER JOIN pg_class AS partitions ON d.objid=partitions.oid
INNER JOIN pg_namespace AS n ON partitions.relnamespace=n.oid
WHERE d.deptype='a'
AND partitions.relispartition=TRUE
GROUP BY table_schema, table_name, partition_strategy, has_default_partition, partition_key_columns
HAVING Count(*)=1
ORDER BY table_schema, table_name;