WITH addresses AS (SELECT table_schema, table_name, column_name,  data_type, character_maximum_length,
CASE WHEN column_name~*'(ip.*addr|ip.*aadr)' AND character_maximum_length<>45 THEN 'ip address can be up to 45 characters'
WHEN column_name~*'(zip_)' AND character_maximum_length<>10 THEN 'zip codes can be up to 10 characters'
WHEN column_name~*'(phone|telef|tel_nr)' AND character_maximum_length<>15 THEN 'phone number can be up to 15 characters'
WHEN column_name~*'e[_| ]*(mail|meil)' AND character_maximum_length<>254 THEN 'e-mail address can be up to 254 characters'
WHEN column_name~*'(addr|aadr)' AND column_name!~*'(ip|phone|telef|mail|meil)' AND character_maximum_length<150 THEN 'place names can be longer'
WHEN column_name~*'(path|tee)' AND column_name!~*'(url|http|ftp)' AND character_maximum_length<260 THEN 'path in older Windows versions can be up to 260 characters, other systems permit even longer'
WHEN column_name~*'(url|http|ftp|veebileht|koduleht|homepage|webpage|website)' AND character_maximum_length<100 THEN 'URLs can be longer'
ELSE 'other' END AS mistake
FROM INFORMATION_SCHEMA.columns
WHERE data_type~*'char' 
AND column_name~*'(zip_|ip.*addr|ip.*aadr|phone|telef|tel_nr|mail|meil|addr|aadr|path|tee|url|http|ftp|veebileht|koduleht|homepage|webpage|website)'
AND column_name!~*'(type|size|profile|prefix|suffix|tyyp|suurus|list|login|username|password|psswd|kasutajanimi|parool|salasona)'
AND (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))
SELECT table_schema, table_name, column_name,  data_type, character_maximum_length, mistake
FROM addresses
WHERE mistake<>'other'
ORDER BY mistake, character_maximum_length, table_schema, table_name;