The list of all the queries

CHECK constraints are inconsistent with DEFAULT values

Query goal: Find table CHECK constraints that involve two columns that have the same default value. However the constraint assumes that the values must be unequal or one value must be bigger than another.
Notes about the query: The query considers both default values that are associated directly with a column as well as default values that are associated through a domain.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: Probably the CHECK constraint has to be changed.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

with ck as (select 
o.conname,
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table, 
c.oid as target_table_oid,
unnest(o.conkey) AS target_col,
substring(pg_get_constraintdef(o.oid),7) as consrc
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'c' 
and cardinality(o.conkey)=2),
ck_names as (select ck.conname, ck.target_schema, ck.target_table, a_target.attname as target_col, ck.consrc
from ck inner join pg_attribute a_target on ck.target_col = a_target.attnum and ck.target_table_oid = a_target.attrelid and a_target.attisdropped = false
where (consrc~'(<|>|<>)' and consrc!~'(>=|<=)' and consrc!~*'not[[:space:]]*\(.*')
or (consrc~'(>=|<=)' and consrc~*'not[[:space:]]*\(.*')),
defaults AS (SELECT c.table_schema, t.table_type, c.table_name, c.column_name,  coalesce(c.column_default, domain_default) AS default_value,
CASE WHEN c.column_default IS NOT NULL THEN 'Column default' ELSE 'Domain default' END AS default_type
FROM information_schema.columns AS c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t  USING (table_schema, table_name)
WHERE c.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND (column_default IS NOT NULL OR domain_default IS NOT NULL))
SELECT conname, target_schema, target_table, string_agg(target_col, ', ' ORDER BY target_col) AS target_col, any_value(default_value), any_value(consrc) AS consrc
FROM ck_names INNER JOIN defaults 
ON ck_names.target_schema=defaults.table_schema 
AND ck_names.target_table=defaults.table_name 
AND ck_names.target_col=defaults.column_name
GROUP BY conname, target_schema, target_table
HAVING Min(default_value)=Max(default_value)
AND Count(*)>1
ORDER BY target_schema, target_table, conname;

Collections where the query belongs to

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

Category nameCategory description
CHECK constraintsQueries of this category provide information about CHECK constraints.
Default valueQueries of this catergory provide information about the use of default values.

The list of all the queries