Find non-foreign key columns of base tables that have a textual type and do not have any simple CHECK constraint, i.e., a constraint that involves only one column. Such columns can contain the empty string and strings that consist of only whitespace.
Notes
The query finds CHECK constraints that are associated with a base table directly as well as CHECK constraints that are associated with domains that are used to define at least one column. The query does not take into account a possibility that the check is a part of a composite CHECK constraint, which contains multiple conditions. The query may give false-negative results if a column does have a check constraint but it does not check empty strings and strings that consist of only whitespace characters.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Add the check constraint to the column. If the column has been defined based on a domain, then add the constraint to the domain.
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
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
FROM text_columns_with_domains_chk),
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
FROM text_columns AS tc
WHERE NOT EXISTS (SELECT 1 FROM text_simple_chk AS tsc WHERE tc.table_schema=tsc.table_schema AND tc.table_name=tsc.table_name
AND tc.column_name=tsc.column_name) AND
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)
ORDER BY table_schema, table_name, ordinal_position;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
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
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
FROM text_columns_with_domains_chk),
text_columns AS (SELECT table_schema, table_name, column_name, data_type, character_maximum_length, ordinal_position
FROM INFORMATION_SCHEMA.columns
WHERE (data_type LIKE '%char%' OR data_type = 'text') AND
(table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE 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 format('SELECT * FROM %1$I.%2$I WHERE %3$I~''^[[:space:]]*$'';', table_schema, table_name, column_name) AS statements
FROM text_columns AS tc
WHERE NOT EXISTS (SELECT 1 FROM text_simple_chk AS tsc WHERE tc.table_schema=tsc.table_schema AND tc.table_name=tsc.table_name
AND tc.column_name=tsc.column_name) AND
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)
ORDER BY table_schema, table_name, ordinal_position;
Find out as to whether the column contains the empty string or string that consist of only whitespace.
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
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
FROM text_columns_with_domains_chk),
text_columns AS (SELECT table_schema, table_name, column_name, data_type, character_maximum_length, ordinal_position, is_nullable
FROM INFORMATION_SCHEMA.columns
WHERE (data_type LIKE '%char%' OR data_type = 'text') AND
(table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE 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 format('UPDATE %1$I.%2$I SET %3$I=NULL WHERE %3$I~''^[[:space:]]*$'';', table_schema, table_name, column_name) AS statements
FROM text_columns AS tc
WHERE is_nullable='YES'
AND NOT EXISTS (SELECT 1 FROM text_simple_chk AS tsc WHERE tc.table_schema=tsc.table_schema AND tc.table_name=tsc.table_name
AND tc.column_name=tsc.column_name) AND
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)
ORDER BY table_schema, table_name, ordinal_position;
Replace the empty sting or whitespace string with NULL.
WITH 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
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')),
text_columns AS (SELECT table_schema, table_name, column_name, data_type, character_maximum_length, domain_name, ordinal_position
FROM INFORMATION_SCHEMA.columns
WHERE (data_type LIKE '%char%' OR data_type = 'text') AND
(table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE 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 format('ALTER TABLE %1$I.%2$I ADD CONSTRAINT chk_%2$s_%3$s CHECK (%3$I!~''^[[:space:]]*$'');', table_schema, table_name , column_name)
FROM text_columns AS tc
WHERE domain_name IS NULL AND
NOT EXISTS (SELECT 1 FROM text_simple_chk AS tsc WHERE tc.table_schema=tsc.table_schema AND tc.table_name=tsc.table_name
AND tc.column_name=tsc.column_name) AND
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)
ORDER BY table_schema, table_name, ordinal_position;
Add the constraint directly to the table column.
SELECT format('ALTER DOMAIN %1$I.%2$I ADD CONSTRAINT chk_%2$s CHECK(VALUE!~''^[[:space:]]*$'');', domain_schema, domain_name) AS statements
FROM INFORMATION_SCHEMA.domains AS d
WHERE (data_type LIKE '%char%' OR data_type = 'text')
AND domain_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 NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.domain_constraints AS dc INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
WHERE d.domain_schema=dc.domain_schema AND d.domain_name=dc.domain_name AND cc.check_clause NOT LIKE '%IS NOT NULL' )
AND EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.columns AS c
WHERE d.domain_schema=c.domain_schema AND d.domain_name=c.domain_name
AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
WHERE 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))
ORDER BY domain_schema, domain_name;
Add the constraint to the domain.
Collections
This query belongs to the following collections:
Name
Description
Find problems about integrity constraints
A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
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.
Data types
Queries of this category provide information about the data types and their usage.
Missing data
Queries of this category provide information about missing data (NULLs) in a database.
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).