Goal Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table.
Notes The query uses information that is collected as the result of statistics collection process (see the ANALYZE statement). The side effect of running the test is that the statistics of the entire database will be refreshed. The query excludes tables that participate in an inheritance hierarchy where the table has a subtable that has at least one row. The reasoning is that the supertable might be an abstract table that is used to define columns of subtables.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT (opens in new tab)
Fixing Suggestion Drop the table or start to use it.
Data Source system catalog only
SQL Query 
ANALYZE;
SELECT n.nspname AS schema_name, c.relname AS table_name
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_catalog.pg_authid u ON u.oid = c.relowner
WHERE c.relkind IN ('r') 
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND (n.nspname = 'public' OR u.rolname <> 'postgres') 
AND reltuples=0
AND NOT EXISTS (SELECT *
FROM pg_inherits pi INNER JOIN pg_class AS pc ON pi.inhrelid=pc.oid
WHERE pi.inhparent=c.oid
AND pc.reltuples>0)
ORDER BY n.nspname, c.relname;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
SELECT format('DROP TABLE %1$I.%2$I;', n.nspname, c.relname) AS statements
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_catalog.pg_authid u ON u.oid = c.relowner
WHERE c.relkind IN ('r')AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND (n.nspname = 'public' OR u.rolname <> 'postgres') AND reltuples=0
ORDER BY n.nspname, c.relname;

Drop the table.

SQL Query to Generate FixDescription
SELECT format('DROP TABLE %1$I.%2$I;', n.nspname, c.relname) AS statements
FROM pg_catalog.pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_catalog.pg_authid u ON u.oid = c.relowner
WHERE c.relkind IN ('r')AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND (n.nspname = 'public' OR u.rolname <> 'postgres') AND reltuples=0
ORDER BY n.nspname, c.relname;
Drop the table.

Collections

This query belongs to the following collections:

Find problems automatically

Queries, 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 .

NameDescription
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

This query is classified under the following categories:

Data at the database logical level

Queries of this category provide information about data in base tables.

Missing data

Queries of this category provide information about missing data (NULLs) in a database.

Unused implementation elements

Queries of this catergory provide information about the database objects that are not used.

NameDescription
Data at the database logical levelQueries of this category provide information about data in base tables.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.
Unused implementation elementsQueries of this catergory provide information about the database objects that are not used.