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)
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:
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.
Validity and completeness
Queries 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).