Find constraints where the use of Trim function is possibly unnecessary. CHECK constraint column!~'^[[:space:]]*$' already ensures that the values in the column cannot be empty strings or strings that consist of only whitespace. Trim(Column)!~'^[[:space:]]*$' - in this case the use of Trim function is unnecessary.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH checks AS (SELECT ccu.table_schema, ccu.table_name, t.table_type, ccu.column_name, cc.check_clause, cc.constraint_name, 'TABLE CHECK' AS check_type, NULL AS domain_schema, NULL AS domain_name
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
WHERE cc.check_clause NOT LIKE '%IS NOT NULL' AND
ccu.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)
UNION SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, cc.check_clause, cc.constraint_name, 'DOMAIN CHECK' AS check_type, cdu.domain_schema, cdu.domain_name
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','FOREIGN') 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))
SELECT table_schema, table_name, table_type, column_name, check_clause, constraint_name, check_type, domain_schema, domain_name
FROM checks
WHERE check_clause~*'TRIM[[:space:]]*[(].*!~[*]{0,1}[[:space:]]*''\^(\[\[:space:\]\]|\\s)\*\$'''
ORDER BY table_schema, table_name,column_name;
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.
Regular expressions
Queries of this catergory provide information about the use of regular expressions.