CHECK constraints with IS NULL

Query goal:

Find CHECK constraints to one column (associated with a base table directly or through domain) that check that the value is missing (IS NULL). Write as simple constraint definitions as possible. By default columns are optional, i.e., they permit NULLs. NULL in a column means that checking of a CHECK constraint on the column results with UNKNOWN. CHECK constraints permit rows in case of which checking results with TRUE or UNKNOWN. In case of a CHECK constraint there is no need to check separately that a value in the column could be missing, i.e., be NULL. Thus, for instance, instead of writing CHECK (price>0 OR price IS NULL) write CHECK (price>0).

Notes about the query:

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).

Query type:

Problem detection (Each row in the result could represent a flaw in the design)

Query reliability:

High (Few or no false-positive results)

Query license:

MIT License

Fixing suggestion:

Remove the IS NULL condition from the constraints. Drop the constraints and recreate these.

Data source:

INFORMATION_SCHEMA+system catalog

SQL query:

Click on query to copy it

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 for generating SQL statements that help us to fix the problem

SQL query	Description
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.

SQL query

Description

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

Collection name	Collection 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 .

Collection name

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

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

Category name

Category description

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.