Goal Find all tables where default value is NULL. NULL is the marker that denotes missing value. Implicitly all columns that do not have a default value have the default NULL. There is no need to specify such default value.
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 default value.
Data Source INFORMATION_SCHEMA only
SQL Query 
SELECT c.table_schema, t.table_type, c.table_name, c.column_name,  c.data_type, c.domain_schema, c.domain_name, c.column_default, domain_default
FROM information_schema.columns AS c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t  USING (table_schema, table_name)
WHERE c.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND (column_default IS NOT NULL OR domain_default IS NOT NULL)
AND (c.column_default~'^NULL::'
OR d.domain_default~'^NULL::')
ORDER BY t.table_type, c.table_schema, c.table_name, c.ordinal_position;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
SELECT format('ALTER %4$s TABLE %1$I.%2$I ALTER COLUMN %3$I DROP DEFAULT;', A.table_schema, A.table_name, A.column_name, 
CASE WHEN B.table_type='FOREIGN' THEN table_type END) AS statements
FROM information_schema.columns A INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
INNER JOIN information_schema.tables B USING (table_schema, table_name)
WHERE A.column_default~'^NULL::'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.table_schema, A.table_name, A.column_name;

Drop the default that is directly associated with the column.

Fix Action #2
SELECT format('ALTER DOMAIN %1$I.%2$I DROP DEFAULT;', D.domain_schema, D.domain_name) AS statements
FROM information_schema.domains D INNER JOIN information_schema.schemata S ON D.domain_schema=S.schema_name
WHERE D.domain_default~'^NULL::'
AND (D.domain_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY domain_schema, domain_name;

Drop the default that is associated with the domain.

SQL Query to Generate FixDescription
SELECT format('ALTER %4$s TABLE %1$I.%2$I ALTER COLUMN %3$I DROP DEFAULT;', A.table_schema, A.table_name, A.column_name, 
CASE WHEN B.table_type='FOREIGN' THEN table_type END) AS statements
FROM information_schema.columns A INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
INNER JOIN information_schema.tables B USING (table_schema, table_name)
WHERE A.column_default~'^NULL::'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY A.table_schema, A.table_name, A.column_name;
Drop the default that is directly associated with the column.
SELECT format('ALTER DOMAIN %1$I.%2$I DROP DEFAULT;', D.domain_schema, D.domain_name) AS statements
FROM information_schema.domains D INNER JOIN information_schema.schemata S ON D.domain_schema=S.schema_name
WHERE D.domain_default~'^NULL::'
AND (D.domain_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY domain_schema, domain_name;
Drop the default that is associated with the domain.

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:

Default value

Queries of this catergory provide information about the use of default values.

Missing data

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

NameDescription
Default valueQueries of this catergory provide information about the use of default values.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.