The list of all the queries

Base table columns permitting empty strings and strings that consist of only whitespace characters

Query goal: 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 about the query: 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.
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: 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: Click on query to copy it

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 for generating SQL statements that help us to fix the problem

SQL queryDescription
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 where the query belongs to

Collection nameCollection description
Find problems about integrity constraintsA 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 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.
Data typesQueries of this category provide information about the data types and their usage.
Missing dataQueries of this category provide information about missing data (NULLs) in a database.
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).

Reference materials for further reading

Reference
https://en.wikipedia.org/wiki/Whitespace_character

The list of all the queries