Data type mismatch in check constraints

Back to Catalog
Goal This query identifies single-column CHECK constraints where the validation logic utilizes operators or functions that are incompatible with the column's native data type. It detects cases where the database must perform implicit casting to evaluate the expression (e.g., performing arithmetic on a TEXT column or string manipulation on a DATE column). Relying on implicit coercion in constraints involves unnecessary computational overhead and frequently indicates a fundamental error in data modeling or constraint formulation.
Notes The query considers only check constraints that are associated with one column.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Fixing Suggestion Perhaps the Boolean expression is incorrect. For instance, CHECK(price::text<>''). In this case drop the constraint and think through what you have to check, whether the check is implemented already with the selection of the data type, and if not then what would be a correct Boolean expression. It could also be that the data type of the column is incorrect and has to be changed.
Data Source system catalog only
SQL Query 
WITH simple_checks AS (SELECT
  (SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
  c.relname AS table_name,
  o.conname AS constraint_name,
  a.attname AS column_name,
  a.attnum AS ordinal_position,
  COALESCE(basetype.typname, t.typname) AS data_type,
  pg_get_constraintdef(o.oid) AS check_clause
FROM pg_constraint o
INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_attribute a ON a.attrelid = o.conrelid AND a.attnum = o.conkey[1]
INNER JOIN pg_type t ON a.atttypid = t.oid
LEFT JOIN pg_type basetype ON t.typtype = 'd' AND t.typbasetype = basetype.oid
WHERE o.contype = 'c' AND cardinality(o.conkey) = 1)
SELECT table_schema, table_name, column_name, data_type, constraint_name, check_clause
FROM simple_checks
WHERE (data_type !~* '(char|text)' 
AND check_clause ~*'::(bpchar|char|text).*(=|>|<|~).*::(bpchar|char|text)' 
AND check_clause !~*'(to_date|to_timestamp)[(][^)]*::(bpchar|character|text)'
AND check_clause !~*'AT[[:space:]]+TIME[[:space:]]+ZONE[[:space:]]+\''Europe/Tallinn\''::text')
OR
(data_type ~* '(char|text)' 
AND check_clause !~*'::(bpchar|char|text|citext)' 
AND check_clause ~*'::')
ORDER BY table_schema, table_name, ordinal_position;

SQL statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
WITH simple_checks AS (SELECT
  (SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
  c.relname AS table_name,
  o.conname AS constraint_name,
  a.attname AS column_name,
  a.attnum AS ordinal_position,
  COALESCE(basetype.typname, t.typname) AS data_type,
  pg_get_constraintdef(o.oid) AS check_clause
FROM pg_constraint o
INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_attribute a ON a.attrelid = o.conrelid AND a.attnum = o.conkey[1]
INNER JOIN pg_type t ON a.atttypid = t.oid
LEFT JOIN pg_type basetype ON t.typtype = 'd' AND t.typbasetype = basetype.oid
WHERE o.contype = 'c' AND cardinality(o.conkey) = 1)
SELECT format('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', table_schema, table_name, constraint_name) AS statements
FROM simple_checks
WHERE (data_type !~* '(char|text)' 
AND check_clause ~*'::(bpchar|char|text).*(=|>|<|~).*::(bpchar|char|text)' 
AND check_clause !~*'(to_date|to_timestamp)[(][^)]*::(bpchar|character|text)'
AND check_clause !~*'AT[[:space:]]+TIME[[:space:]]+ZONE[[:space:]]+\''Europe/Tallinn\''::text')
OR
(data_type ~* '(char|text)' 
AND check_clause !~*'::(bpchar|char|text|citext)' 
AND check_clause ~*'::')
ORDER BY table_schema, table_name, constraint_name;
Drop the constraint.
Collections

This query belongs to the following collections:

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:

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
Data typesQueries of this category provide information about the data types and their usage.
Validity and completenessQueries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).