Goal This query identifies single-column CHECK constraints (applied either directly to a base table or through a domain) that explicitly check for NULL values. Constraint definitions should be kept as simple as possible. Because a NULL value causes a logical condition to evaluate to UNKNOWN, and CHECK constraints inherently allow rows that evaluate to either TRUE or UNKNOWN, there is no need to explicitly allow missing values. For instance, instead of writing CHECK (price > 0 OR price IS NULL), the constraint should simply be written as CHECK (price > 0).
Notes The query considers only constraints that are associated with exactly one column. In case of constraints on multiple columns there could be a complex condition that requires the use of IS NULL predicate. For instance, CHECK (a IS NULL AND b IS NOT NULL OR a IS NOT NULL AND b IS NULL).
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 Remove the IS NULL condition from the constraints. Drop the constraints and recreate these.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH expressions AS (select 
n.nspname as schema,
c.relname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'TABLE CHECK' AS type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where o.contype ='c' and cardinality(o.conkey)=1
and n.nspname 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 n.nspname as schema,
t.typname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'DOMAIN CHECK' AS type
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
where o.contype ='c' and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null))
SELECT schema, name, expression, type
FROM expressions
WHERE expression~*'IS[[:space:]]*NULL'
ORDER BY schema, type, name;

SQL statements that help generate fixes for the identified problem.

Fix Action #1
SELECT format('ALTER TABLE  %1$I.%2$I DROP CONSTRAINT %3$I;',  n.nspname, c.relname, o.conname) AS statements
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where o.contype ='c' and cardinality(o.conkey)=1
and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
and substring(pg_get_constraintdef(o.oid),7)~*'IS[[:space:]]*NULL'
ORDER BY n.nspname, c.relname, o.conname;

Drop the check constraint that is associated directly with the base table.

Fix Action #2
SELECT format('ALTER DOMAIN  %1$I.%2$I DROP CONSTRAINT %3$I;',  n.nspname, t.typname, o.conname) AS statements
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
where o.contype ='c' and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
and substring(pg_get_constraintdef(o.oid),7)~*'IS[[:space:]]*NULL'
ORDER BY n.nspname, t.typname, o.conname;

Drop the domain check constraint.

SQL Query to Generate FixDescription
SELECT format('ALTER TABLE  %1$I.%2$I DROP CONSTRAINT %3$I;',  n.nspname, c.relname, o.conname) AS statements
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where o.contype ='c' and cardinality(o.conkey)=1
and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
and substring(pg_get_constraintdef(o.oid),7)~*'IS[[:space:]]*NULL'
ORDER BY n.nspname, c.relname, o.conname;
Drop the check constraint that is associated directly with the base table.
SELECT format('ALTER DOMAIN  %1$I.%2$I DROP CONSTRAINT %3$I;',  n.nspname, t.typname, o.conname) AS statements
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
where o.contype ='c' and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
and substring(pg_get_constraintdef(o.oid),7)~*'IS[[:space:]]*NULL'
ORDER BY n.nspname, t.typname, o.conname;
Drop the domain check constraint.

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:

CHECK constraints

Queries of this category provide information about CHECK constraints.

Missing data

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

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.