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)
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.
SQL Query to Generate Fix
Description
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:
Name
Description
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 .
Categories
This query is classified under the following categories:
Name
Description
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.