This query identifies multiple-column CHECK constraints that explicitly check for NULL values. In general, 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 usually no need to explicitly allow missing values. For instance, instead of writing CHECK (last_update_time IS NULL OR last_update_time >= creation_time), the constraint should simply be written as CHECK (last_update_time >= creation_time). However, a valid exception exists for implication rules (P ⇒ Q), which can be rewritten as NOT (P) OR Q. For example, the constraint CHECK (NOT (product IS NOT NULL) OR service IS NULL) properly enforces the rule that if a product is present, the service must be NULL. Such logical constructs are entirely appropriate.
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 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))
SELECT schema, name, expression, type
FROM expressions
WHERE
expression ~* '(\y[a-zA-Z_][a-zA-Z0-9_]*\y)\W+IS\s+NULL\y.*\yOR\y.*\y\1\y'
OR
expression ~* '(\y[a-zA-Z_][a-zA-Z0-9_]*\y)\y.*\yOR\y.*\y\1\W+IS\s+NULL\y'
ORDER BY schema, type, name;
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.