Inadequate length constraints on address-related columns

Back to Catalog
Goal This query identifies base table columns designated for storing address components (e.g., ip addres e-mail, telephone number, physical location) that lack appropriate length constraints reflecting real-world data requirements. It operates on a heuristic basis, targeting columns whose identifiers imply address data (e.g., names containing "addr" or "mail") but whose definitions fail to account for standard maximum lengths. This includes both insufficient allocation (truncation risk) and unbounded allocation (data quality risk). Ensuring these fields are sized according to domain standards is crucial for data integrity and usability.
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. The query considers both column names in English and Estonian.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Fixing Suggestion 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|mobil|mobiil|telef|tel_nr)' AND character_maximum_length>20 THEN 'A phone number can consist of up to 15 digits. Additionally, it may include a leading plus sign and spaces or hyphens for readability.'
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 FixDescription
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:

NameDescription
Find problems about base tablesA 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 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

This query is classified under the following categories:

NameDescription
Field sizeQueries of this category provide information about the maximum size of values that can be recorded in column fields
Result quality depends on namesQueries 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 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).