Base table columns permitting telephone numbers without digits

WITH domains 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_catalog, constraint_schema, constraint_name)
WHERE t.table_type='BASE TABLE' 
AND cc.check_clause NOT LIKE '%IS NOT NULL'  
AND cdu.column_name~*'(phone|telef|tel_nr)' 
AND cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND catalog_name IS NOT NULL 
AND schema_name IS NOT NULL)),

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 a.attname~*'(phone|telef|tel_nr)'
UNION SELECT table_schema, table_name, column_name, data_type, check_clause
FROM domains),

suspected_columns AS (SELECT table_schema, table_name, column_name, data_type
FROM INFORMATION_SCHEMA.columns
WHERE (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='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) 
AND column_name~* '(phone|telef|tel_nr)'
AND (data_type='text' OR data_type ILIKE '%char%')),

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
FROM suspected_columns AS pc
WHERE NOT EXISTS (SELECT 1 FROM simple_chk AS psc WHERE pc.table_schema=psc.table_schema AND pc.table_name=psc.table_name
AND pc.column_name=psc.column_name AND check_clause ~ '(0-9|[:](digit|alnum)[:]|\\d)') 
AND NOT EXISTS (SELECT 1 FROM fk_columns AS f WHERE pc.table_schema=f.table_schema AND pc.table_name=f.table_name
AND pc.column_name=f.column_name)
ORDER BY table_schema, table_name, column_name;

Query goal:	Find non-foreign key base table columns that name refers to the possibility that these are used to register phone numbers. Find the columns that do not have any simple CHECK constraint that references to the character class of digits. A simple check constraint covers a single column. In this case registration of e-mail addresses without digits is most probably not prohibited.
Notes about the query:	The query takes into account only constraints that are associated with exactly one column. Thus, there could be constraints that apply restrictions to multiple columns that prevent e-mail addresses without @ signs. The query takes into account constraints that are associated directly with the column as well as constraints that are associated with the column through a domain. The query considers both column names in English and Estonian.
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
Data source:	INFORMATION_SCHEMA+system catalog
SQL query:	Click on query to copy it WITH domains 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_catalog, constraint_schema, constraint_name) WHERE t.table_type='BASE TABLE' AND cc.check_clause NOT LIKE '%IS NOT NULL' AND cdu.column_name~'(phone\|telef\|tel_nr)' AND cdu.table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND catalog_name IS NOT NULL AND schema_name IS NOT NULL)), 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 a.attname~'(phone\|telef\|tel_nr)' UNION SELECT table_schema, table_name, column_name, data_type, check_clause FROM domains), suspected_columns AS (SELECT table_schema, table_name, column_name, data_type FROM INFORMATION_SCHEMA.columns WHERE (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='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) AND column_name~* '(phone\|telef\|tel_nr)' AND (data_type='text' OR data_type ILIKE '%char%')), 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 FROM suspected_columns AS pc WHERE NOT EXISTS (SELECT 1 FROM simple_chk AS psc WHERE pc.table_schema=psc.table_schema AND pc.table_name=psc.table_name AND pc.column_name=psc.column_name AND check_clause ~ '(0-9\|[:](digit\|alnum)[:]\|\\d)') AND NOT EXISTS (SELECT 1 FROM fk_columns AS f WHERE pc.table_schema=f.table_schema AND pc.table_name=f.table_name AND pc.column_name=f.column_name) ORDER BY table_schema, table_name, column_name;

Category name	Category description
CHECK constraints	Queries of this category provide information about CHECK constraints.
Result quality depends on names	Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
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).

Base table columns permitting telephone numbers without digits

Collections where the query belongs to

Categories where the query belongs to