Empty columns

Query goal:

Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all.

Notes about the query:

The query does not look up the actual data but relies on 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.

Query type:

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

Query reliability:

Medium (Medium number of false-positive results)

Query license:

MIT License

Fixing suggestion:

Drop the column or start to use it.

Data source:

INFORMATION_SCHEMA+system catalog

SQL query:

Click on query to copy it

ANALYZE;
SELECT ps.schemaname, ps.tablename, ps.attname 
FROM pg_catalog.pg_stats AS ps INNER JOIN pg_catalog.pg_stat_all_tables AS psat
ON ps.schemaname=psat.schemaname AND ps.tablename=psat.relname
WHERE  ps.schemaname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND n_distinct = 0 AND psat.n_live_tup>0 
AND psat.last_analyze IS NOT NULL
ORDER BY ps.schemaname, ps.tablename;

SQL statements for generating SQL statements that help us to fix the problem

SQL query	Description
SELECT format('ALTER TABLE %1$I.%2$I DROP COLUMN %3$I;', ps.schemaname, ps.tablename, ps.attname) AS statements FROM pg_catalog.pg_stats AS ps INNER JOIN pg_catalog.pg_stat_all_tables AS psat ON ps.schemaname=psat.schemaname AND ps.tablename=psat.relname WHERE ps.schemaname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND n_distinct = 0 AND psat.n_live_tup>0 AND psat.last_analyze IS NOT NULL ORDER BY ps.schemaname, ps.tablename;	Drop the column.

SQL query

Description

SELECT format('ALTER TABLE %1$I.%2$I DROP COLUMN %3$I;', ps.schemaname, ps.tablename, ps.attname) AS statements
FROM pg_catalog.pg_stats AS ps INNER JOIN pg_catalog.pg_stat_all_tables AS psat
ON ps.schemaname=psat.schemaname AND ps.tablename=psat.relname
WHERE  ps.schemaname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND n_distinct = 0 AND psat.n_live_tup>0 
AND psat.last_analyze IS NOT NULL
ORDER BY ps.schemaname, ps.tablename;

Drop the column.

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.