PostgreSQL 42622 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 42622: Name Too Long

PostgreSQL error code 42622 occurs when a database identifier—such as a table name, column name, index name, or function name—exceeds the maximum allowed length of 63 bytes. This limit is defined by the internal constant NAMEDATALEN (default: 64), with one byte reserved for the null terminator. It's a surprisingly common pitfall when using ORMs, migration tools, or multibyte character sets like UTF-8.

Top 3 Causes

1. Object Names Exceeding 63 Bytes

The most straightforward cause: any identifier longer than 63 bytes triggers this error immediately.

-- This will raise ERROR 42622
CREATE TABLE this_is_an_extremely_long_table_name_that_exceeds_the_limit (
    id SERIAL PRIMARY KEY
);
-- ERROR:  42622: identifier "this_is_an_extremely_long_table_name_that_exceeds_the_limit"
--         exceeds maximum allowed length of 63

-- Check byte length before creating
SELECT octet_length('this_is_an_extremely_long_table_name_that_exceeds_the_limit') 
    AS byte_length;
-- Returns: 60 (OK in this case, but close to the limit)

-- Safe fix: use a shorter name
CREATE TABLE long_name_table (
    id SERIAL PRIMARY KEY
);

2. Auto-generated Index or Constraint Names from ORMs

ORMs like Django, SQLAlchemy, and Hibernate auto-generate index and constraint names by combining table and column names. This frequently results in names exceeding 63 bytes without any warning during development.

-- Auto-generated name pattern that causes the error
CREATE TABLE customer_transaction_records (
    id SERIAL PRIMARY KEY,
    customer_identification_code INTEGER,
    transaction_category_type VARCHAR(50)
);

-- Auto-generated index name would be too long
CREATE INDEX ON customer_transaction_records
    (customer_identification_code, transaction_category_type);
-- PostgreSQL would try to name it:
-- customer_transaction_records_customer_identification_code_tran...
-- ERROR: 42622

-- Fix: always specify index names explicitly
CREATE INDEX idx_ctr_cust_cat
    ON customer_transaction_records
    (customer_identification_code, transaction_category_type);

-- Also name constraints explicitly
ALTER TABLE customer_transaction_records
    ADD CONSTRAINT fk_ctr_customer
    FOREIGN KEY (customer_identification_code)
    REFERENCES customers(id);

3. Multibyte Characters (UTF-8) in Identifiers

PostgreSQL's 63-byte limit counts bytes, not characters. In UTF-8, a single Korean, Chinese, or Japanese character takes 3 bytes, meaning you can only use 21 multibyte characters before hitting the limit.

-- Check character count vs byte count
SELECT
    char_length('사용자거래내역관리테이블') AS char_count,
    octet_length('사용자거래내역관리테이블') AS byte_count;
-- char_count: 12, byte_count: 36 (safe)

-- 22 Korean characters = 66 bytes → ERROR 42622
-- Fix: use shorter names or switch to ASCII identifiers
CREATE TABLE 사용자거래 (   -- 5 chars = 15 bytes (safe)
    id SERIAL PRIMARY KEY
);

-- Best practice: use ASCII identifiers
CREATE TABLE user_transactions (
    id SERIAL PRIMARY KEY
);

Quick Fix Solutions

-- Rename an existing object that has a too-long name
ALTER TABLE old_very_long_table_name_that_exceeds_limit
    RENAME TO short_table_name;

-- Audit your database for potentially problematic long names
SELECT
    relname        AS object_name,
    octet_length(relname) AS byte_length,
    relkind        AS type
FROM pg_class
WHERE octet_length(relname) > 50
ORDER BY byte_length DESC;

-- Check index names specifically
SELECT
    indexname,
    octet_length(indexname) AS byte_length
FROM pg_indexes
WHERE octet_length(indexname) > 50
ORDER BY byte_length DESC;

Prevention Tips

1. Enforce a naming convention with a hard byte limit.
Set a team standard that all identifiers must stay under 40 bytes (giving yourself a comfortable safety margin). Integrate a DDL linter like sqlfluff into your CI/CD pipeline to catch violations before they reach production.

-- Utility function to validate identifier length during development
CREATE OR REPLACE FUNCTION validate_identifier(name TEXT)
RETURNS VOID AS $$
BEGIN
    IF octet_length(name) > 63 THEN
        RAISE EXCEPTION 'Identifier "%" is % bytes, exceeds 63-byte limit',
            name, octet_length(name);
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT validate_identifier('my_proposed_index_name_here');

2. Always explicitly name indexes and constraints in ORMs.
Never rely on auto-generated names from Django, SQLAlchemy, or Hibernate. Always provide explicit name parameters for indexes, foreign keys, and unique constraints. This not only prevents 42622 errors but also makes migrations more predictable and reversible.

Related Errors

42601 (syntax_error): General SQL syntax errors, often encountered alongside 42622 during DDL operations.
42710 (duplicate_object): Triggered when renaming a long identifier to a shorter one that already exists.
42939 (reserved_name): Raised when using a PostgreSQL reserved keyword as an identifier—another naming pitfall to watch for when establishing naming conventions.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community