The list of all the queries

NOT NULL constraint via CHECK instead of NOT NULL constraint

Query 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 about the query: The query considers both constraints that are associated with a domain as well as constraints that are directly attached to a table.
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
Data source: INFORMATION_SCHEMA only
SQL query: Click on query to copy it

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 where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
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.

The list of all the queries