Find CHECK constraints that unnecessarily check that a value is missing. In a CHECK constraint, it is not necessary to check whether a value is NULL, because a CHECK constraint rejects rows where the condition evaluates to FALSE and allows rows where the condition evaluates to TRUE or UNKNOWN. If a value is missing (i.e., NULL), the result of the check is UNKNOWN. Therefore, for example, CHECK (price IS NULL OR price > 0) is equivalent to CHECK (price > 0).
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.