Declare NOT NULL constraint either directly to the column or to the domain of the column. Do not declare the NOT NULL constraint to both column and domain.
Data Source
INFORMATION_SCHEMA only
SQL Query
SELECT A.table_schema, A.table_name , A.column_name, CASE WHEN A.data_type ILIKE 'character%' AND A.character_maximum_length IS NOT NULL THEN A.data_type || '(' || A.character_maximum_length::text || ')'
WHEN A.data_type ILIKE 'timestamp%' AND A.datetime_precision IS NOT NULL THEN A.data_type || '(' || A.datetime_precision || ')'
WHEN A.data_type ILIKE 'numeric%' AND A.numeric_precision IS NOT NULL THEN A.data_type || '(' || A.numeric_precision::text || ',' ||coalesce(A.numeric_scale,0)::text || ')'
WHEN A.data_type ILIKE 'interval%' AND A.interval_type IS NOT NULL THEN A.data_type || '(' || A.interval_type::text || ')'
WHEN A.data_type='USER-DEFINED' THEN A.udt_schema || '.' || A.udt_name
ELSE A.data_type END AS data_type,
D.domain_name, D.domain_schema, A.column_default, D.domain_default
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN information_schema.domains D USING (domain_schema, domain_name)
WHERE coalesce(column_default, domain_default) IS NOT NULL
AND coalesce(column_default, domain_default)!~*'^''[[:space:]]*''::'
AND is_nullable='YES'
AND T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY table_schema, table_name, ordinal_position;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET NOT NULL;', A.table_schema, A.table_name , A.column_name) AS statements
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
LEFT JOIN information_schema.domains D USING (domain_schema, domain_name)
WHERE coalesce(column_default, domain_default) IS NOT NULL
AND is_nullable='YES'
AND column_default!~*'^''[[:space:]]*''::'
AND T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY table_schema, table_name, column_name;
Declare NOT NULL constraint to the column.
SELECT DISTINCT format('ALTER DOMAIN %1$I.%2$I SET NOT NULL;', D.domain_schema, D.domain_name) AS statements
FROM information_schema.columns A INNER JOIN information_schema.tables T USING (table_schema, table_name)
INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name
INNER JOIN information_schema.domains D USING (domain_schema, domain_name)
WHERE coalesce(column_default, domain_default) IS NOT NULL
AND is_nullable='YES'
AND domain_default!~*'^''[[:space:]]*''::'
AND T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
ORDER BY statements;
Declare NOT NULL constraint to the domain.
Collections
This query belongs to the following collections:
Name
Description
Find problems about integrity constraints
A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
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.
Validity and completeness
Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).