Empty tables

Query 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 about the query:

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.

Query type:

Problem detection (Each row in the result could represent a flaw in the design)

Query reliability:

High (Few or no false-positive results)

Query license:

MIT License

Fixing suggestion:

Drop the table or start to use it.

Data source:

system catalog only

SQL query:

Click on query to copy it

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 for generating SQL statements that help us to fix the problem

SQL query	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.

SQL query

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 where the query belongs to

Collection name	Collection 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 .

Collection name

Collection 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 where the query belongs to

Category name	Category 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.

Category name

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