This query identifies CHECK constraints that contain redundant logic for handling NULLs, a pattern often arising from a misunderstanding of SQL's three-valued logic (TRUE, FALSE, UNKNOWN). A CHECK constraint's condition only fails on FALSE, implicitly permitting NULLs by evaluating comparisons to UNKNOWN. Consequently, an explicit OR column IS NULL clause is tautological and adds no functional value, as demonstrated by the equivalence of CHECK (price IS NULL OR price > 0) and CHECK (price > 0). Removing this superfluous logic improves constraint clarity and eliminates a marginal but unnecessary computational step.
Notes
The query considers both table and domain CHECK constraints. The query tries to exclude constraints in the form NOT (P) OR Q, because these implement association rules P=>Q
Type
Problem detection (Each row in the result could represent a flaw in the design)
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 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 AS object_identifier, type AS object_type, expression AS suspected_expression
FROM expressions
WHERE expression~'(?
Collections
This query belongs to the following collections:
Name
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
This query is classified under the following categories:
Name
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.