Find base table columns that are meant for recording different types of addresses where the filed size does not take into account the possible maximum length.
Notes
The query uses column name in order to decide as to whether the column is meant for recording e-mail addresses. It may cause false positive or false negative results.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Set the e-mail field size to 254 characters. It is possible that one has to also fix registered values and recreate derived tables and routines.
Data Source
INFORMATION_SCHEMA only
SQL Query
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;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET DATA TYPE VARCHAR(254);', table_schema, table_name, column_name) AS statements
FROM INFORMATION_SCHEMA.columns
WHERE (data_type='character varying') AND
(table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND
column_name ~*'e[_| ]*(mail|meil)' AND
character_maximum_length<>254 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 table_schema, table_name, ordinal_position;
Change the field size.
Collections
This query belongs to the following collections:
Name
Description
Find problems about base tables
A selection of queries that return information about the data types, field sizes, default values as well as general structure of base tables. 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
Field size
Queries of this category provide information about the maximum size of values that can be recorded in column fields
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).