Goal Find columns where the NOT NULL constraint has been added with the help of an explicit CHECK constraints instead of a NOT NULL constraint. One should note that internally NOT NULL constraints are treated as a kind of CHECK constraints. Nevertheless, the design should be consistent (i.e., NOT NULL is enforced with the same way in different places).
Notes The query considers both constraints that are associated with a domain as well as constraints that are directly attached to a table.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Data Source INFORMATION_SCHEMA only
SQL Query
WITH checks AS (SELECT ccu.table_schema, ccu.table_name, t.table_type, ccu.column_name, cc.check_clause, cc.constraint_name, 'TABLE CHECK' AS check_type, NULL AS domain_schema, NULL AS domain_name
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
WHERE ccu.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)
UNION SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, cc.check_clause, cc.constraint_name, 'DOMAIN CHECK' AS check_type, cdu.domain_schema, cdu.domain_name
FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
WHERE t.table_type IN ('BASE TABLE','FOREIGN') 
AND cdu.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)),
optional_columns AS (SELECT A.table_schema, A.table_name , A.column_name
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
WHERE is_nullable='YES' AND T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres'))
SELECT table_schema, table_name, table_type, column_name, check_clause, check_clause, constraint_name, check_type, domain_schema, domain_name
FROM checks
WHERE check_clause~*'IS[[:space:]]+NOT[[:space:]]+NULL'
AND check_clause!~*'[[:space:]](AND|OR)[[:space:]]'
AND (table_schema, table_name, column_name) IN (SELECT table_schema, table_name, column_name
FROM optional_columns)
ORDER BY table_schema, table_name, column_name;

Collections

This query belongs to the following collections:

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:

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
DomainsQueries of this category provide information about reusable specifications of column properties.
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.