Base table column of personal names does not restrict the maximum character length

Back to Catalog
Goal This query identifies base table columns that, based on their name, are presumed to store personal names but lack an explicit maximum length constraint. It operates on a heuristic, flagging columns with names like first_name, surname, etc., that are defined with unbounded textual types (e.g., text, varchar) and have no corresponding CHECK constraint to limit their length (e.g., char_length(col) <= n). The absence of such a limit is a design flaw that can introduce usability issues in front-end applications and create potential security vulnerabilities related to excessive data submission.
Notes The query finds columns with type text or varchar that do not restrict the maximum length with a CHECK constraint.
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 Add a check constraint to restrict the maximum character length.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
WITH column_checks AS (SELECT
    ns.nspname AS table_schema,
    cls.relname AS table_name,
    a.attname AS column_name,
    COALESCE(basetype.typname, t.typname) AS data_type,
    pg_get_constraintdef(c.oid) AS check_clause
FROM
    pg_constraint c
INNER JOIN
    pg_class cls ON c.conrelid = cls.oid
INNER JOIN
    pg_namespace ns ON cls.relnamespace = ns.oid
INNER JOIN
    pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
INNER JOIN
    pg_type t ON a.atttypid = t.oid
LEFT JOIN
    pg_type basetype ON t.typtype = 'd' AND t.typbasetype = basetype.oid
WHERE
    c.contype = 'c'
    AND c.conrelid != 0
    AND cardinality(c.conkey) = 1

UNION ALL

SELECT
    ns.nspname AS table_schema,
    cls.relname AS table_name,
    a.attname AS column_name,
    basetype.typname AS data_type,
    pg_get_constraintdef(c.oid) AS check_clause
FROM
    pg_attribute a
INNER JOIN
    pg_class cls ON a.attrelid = cls.oid
INNER JOIN
    pg_namespace ns ON cls.relnamespace = ns.oid
INNER JOIN
    pg_type t ON a.atttypid = t.oid
INNER JOIN
    pg_constraint c ON c.contypid = t.oid
INNER JOIN
    pg_type basetype ON t.typbasetype = basetype.oid
WHERE
    cls.relkind IN ('r', 'p', 'f')
    AND a.attnum > 0
    AND NOT a.attisdropped
    AND t.typtype = 'd'
    AND c.contype = 'c'),
column_length_checks AS (SELECT table_schema, table_name, column_name, data_type, check_clause
FROM column_checks
WHERE check_clause~'(length.*<(=){0,1}.*(?|\{,[[:digit:]]+\})')

SELECT c.table_schema, c.table_name, c.column_name,  c.data_type, c.character_maximum_length
FROM information_schema.columns AS c
WHERE (c.table_schema, c.table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND 
c.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 c.column_name~*'^[_]*(eesnimi|perenimi|perekonnanimi|firstname|first_name|given_name|givenname|surname|family_name|last_name|lastname)[_]*$'
AND (c.data_type='text' OR (c.data_type='character varying' AND c.character_maximum_length IS NULL))
AND NOT EXISTS (SELECT *
FROM column_length_checks AS clc
WHERE clc.table_schema=c.table_schema
AND clc.table_name=c.table_name
AND clc.column_name=c.column_name)
ORDER BY c.table_schema, c.table_name, c.ordinal_position;
Collections

This query belongs to the following collections:

NameDescription
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
CHECK constraintsQueries of this category provide information about CHECK constraints.
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).