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)
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.
SQL Query to Generate Fix
Description
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:
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
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.