The list of all the queries

Declaratively partitioned tables with one partition

Query goal: Find declaratively partitioned tables that have exactly one partition. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. If there is only one partition, then it raises question as to why the additional complexity associated with partitioning is needed.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Unspecified (The query is not used for detecting flaws or the reliability is unknown)
Query license: MIT License
Fixing suggestion: Create additional partitions or drop the table and recreate it without partitioning.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

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;

Collections where the query belongs to

Collection nameCollection description
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories where the query belongs to

Category nameCategory description
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.

Reference materials for further reading

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

The list of all the queries