Goal Find non-foreign key columns of base tables that have a textual type and do not have a simple CHECK constraint (i.e., a constraint that involves only one column) that seems to prohibit empty strings and strings that consist of only whitespace as well as a simple CHECK constraint that specifies permitted symbols.
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
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH columns_with_domains_chk AS (
SELECT cdu.table_schema, cdu.table_name, cdu.column_name, (SELECT data_type FROM INFORMATION_SCHEMA.domains AS d
WHERE d.domain_schema=dc.domain_schema AND d.domain_name=dc.domain_name) AS data_type, cc.check_clause
FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
WHERE t.table_type IN ('BASE TABLE') 
AND cc.check_clause NOT LIKE '%IS NOT NULL'    
AND cdu.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)),

text_columns_with_domains_chk AS (
SELECT table_schema, table_name, column_name, data_type, check_clause
FROM columns_with_domains_chk
WHERE data_type LIKE '%char%' OR data_type = 'text'),

text_simple_chk AS (SELECT 
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
c.relname as table_name, 
a.attname AS column_name,
t.typname AS type_name,
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 = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
INNER JOIN pg_type t ON a.atttypid=t.oid
WHERE cardinality(o.conkey)=1 
AND o.contype = 'c' 
AND t.typname IN ('bpchar','char','varchar', 'text')
UNION SELECT table_schema, table_name, column_name, data_type, check_clause
FROM text_columns_with_domains_chk),

text_empty_chk AS (SELECT table_schema, table_name, column_name, type_name
FROM text_simple_chk
WHERE check_clause~'!~.*(\\s|\[\[:space:\]\])'),

text_pattern_chk AS (SELECT table_schema, table_name, column_name, type_name
FROM text_simple_chk
WHERE check_clause~'[[:space:]](~[*]{0,1}|~~[*]{0,1})[[:space:]]'),

text_columns AS (SELECT table_schema, table_name, table_type, column_name, data_type, character_maximum_length, is_nullable, ordinal_position
FROM INFORMATION_SCHEMA.columns INNER JOIN INFORMATION_SCHEMA.tables USING (table_schema, table_name)
WHERE (data_type LIKE '%char%' OR data_type = 'text') 
AND table_type IN ('BASE TABLE') AND 
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)),

fk_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name
from (select 
(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
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype = 'f') t
inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false)

SELECT  table_schema, table_name, column_name, data_type, character_maximum_length, is_nullable, (SELECT string_agg(check_clause,',
') AS checks FROM text_simple_chk AS tsc WHERE tc.table_schema=tsc.table_schema AND tsc.table_name=tc.table_name AND tsc.column_name=tc.column_name) AS checks FROM text_columns AS tc WHERE NOT EXISTS (SELECT 1 FROM fk_columns AS f WHERE tc.table_schema=f.table_schema AND tc.table_name=f.table_name AND tc.column_name=f.column_name) AND NOT EXISTS (SELECT 1 FROM text_empty_chk AS tec WHERE tc.table_schema=tec.table_schema AND tc.table_name=tec.table_name AND tc.column_name=tec.column_name) AND NOT EXISTS (SELECT 1 FROM text_pattern_chk AS tpc WHERE tc.table_schema=tpc.table_schema AND tc.table_name=tpc.table_name AND tc.column_name=tpc.column_name) ORDER BY table_schema, table_name, ordinal_position; WITH columns_with_domains_chk AS ( SELECT cdu.table_schema, cdu.table_name, cdu.column_name, (SELECT data_type FROM INFORMATION_SCHEMA.domains AS d WHERE d.domain_schema=dc.domain_schema AND d.domain_name=dc.domain_name) AS data_type, cc.check_clause FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name) INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name) INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name) WHERE t.table_type IN ('BASE TABLE') AND cc.check_clause NOT LIKE '%IS NOT NULL' AND cdu.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)), text_columns_with_domains_chk AS ( SELECT table_schema, table_name, column_name, data_type, check_clause FROM columns_with_domains_chk WHERE data_type LIKE '%char%' OR data_type = 'text'), text_simple_chk AS (SELECT (SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema, c.relname as table_name, a.attname AS column_name, t.typname AS type_name, 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 = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE INNER JOIN pg_type t ON a.atttypid=t.oid WHERE cardinality(o.conkey)=1 AND o.contype = 'c' AND t.typname IN ('bpchar','char','varchar', 'text') UNION SELECT table_schema, table_name, column_name, data_type, check_clause FROM text_columns_with_domains_chk), text_empty_chk AS (SELECT table_schema, table_name, column_name, type_name FROM text_simple_chk WHERE check_clause~'!~\*{0,1}[[:space:]]*''\^(\\s|\[\[:space:\]\])(\*|\+)\$'''), text_pattern_chk AS (SELECT table_schema, table_name, column_name, type_name FROM text_simple_chk WHERE check_clause~'[[:space:]](~[*]{0,1}|~~[*]{0,1})[[:space:]]'), text_columns AS (SELECT table_schema, table_name, table_type, column_name, data_type, character_maximum_length, is_nullable, ordinal_position FROM INFORMATION_SCHEMA.columns INNER JOIN INFORMATION_SCHEMA.tables USING (table_schema, table_name) WHERE (data_type LIKE '%char%' OR data_type = 'text') AND table_type IN ('BASE TABLE') AND 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)), fk_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (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 from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype = 'f') t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false) SELECT table_schema, table_name, column_name, data_type, character_maximum_length, is_nullable, (SELECT string_agg(check_clause,',
') AS checks FROM text_simple_chk AS tsc WHERE tc.table_schema=tsc.table_schema AND tsc.table_name=tc.table_name AND tsc.column_name=tc.column_name) AS checks FROM text_columns AS tc WHERE NOT EXISTS (SELECT 1 FROM fk_columns AS f WHERE tc.table_schema=f.table_schema AND tc.table_name=f.table_name AND tc.column_name=f.column_name) AND NOT EXISTS (SELECT 1 FROM text_empty_chk AS tec WHERE tc.table_schema=tec.table_schema AND tc.table_name=tec.table_name AND tc.column_name=tec.column_name) AND NOT EXISTS (SELECT 1 FROM text_pattern_chk AS tpc WHERE tc.table_schema=tpc.table_schema AND tc.table_name=tpc.table_name AND tc.column_name=tpc.column_name) ORDER BY table_schema, table_name, ordinal_position;
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.
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).