Inappropriate length constraints for address columns

Back to Catalog
Goal This query identifies base table columns designated for storing address components (e.g., IP addresses, emails, telephone numbers, or physical locations) that have inappropriate length constraints. It flags columns where the defined field size is either too strict or too loose compared to real-world data requirements. Operating on a heuristic basis, the query targets columns whose names imply address data (e.g., containing 'addr' or 'mail') but whose definitions fail to align with standard lengths. Highlighting both insufficient allocation (truncation risk) and unbounded allocation (data quality risk) helps ensure that these fields are sized according to domain standards, which 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
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 OR character_maximum_length<15) 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. Use 20 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. Use 150 charcters'
WHEN column_name~*'(path|(?'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;
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).