阅读中文版本

PostgreSQL ERROR 22001 — Value Too Long for Column

Fix PostgreSQL 22001 by widening varchar columns or using text, and validating length with character-based limits.

psql -c "INSERT INTO t(v) VALUES (repeat('a', 60))"

psql -c "\d t"

Symptoms

This guide profiles the failure behind “PostgreSQL ERROR 22001 — Value Too Long for Column”. It walks through the observable symptoms, a reproducible diagnosis path, the underlying root cause, the concrete fix, and how to verify the change and prevent a regression.

Observed symptoms include:

• ERROR: value too long for type character varying(50) (SQLSTATE 22001)
• Short values insert fine, longer ones fail
• A migration changed the column type and reads/writes now fail

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Read the type limit from the error

psql -c "INSERT INTO t(v) VALUES (repeat('a', 60))"

The message states the exact column type and its limit, telling you how much to widen.

Step 2: Check the column definition

psql -c "\d t"

Confirming the type shows whether to widen varchar or switch to text.

Likely contributors, in order of frequency:

• The value exceeds the declared varchar(n) length in characters
• The column was narrowed by a migration while existing code still writes longer values
• Truncation is not automatic; PostgreSQL rejects rather than silently cutting

Root Cause

PostgreSQL enforces length constraints on varchar(n) and char(n) strictly, raising SQLSTATE 22001 when a value is longer. It never truncates implicitly, so the fix is to widen the column or bound the input.

The Fix

Widen the column or use text with an explicit CHECK, validate input length in the application, and coordinate type changes with all writers during migration.

Watch out: Using unlimited text everywhere removes a useful guard; add a CHECK constraint when a business limit exists.

ALTER TABLE t ALTER COLUMN v TYPE varchar(200);

-- or text with an explicit business limit
ALTER TABLE t ALTER COLUMN v TYPE text;
ALTER TABLE t ADD CONSTRAINT v_len CHECK (char_length(v) <= 200);

-- read-only widening without locking (PG 11+)
ALTER TABLE t ALTER COLUMN v TYPE varchar(200) USING v::varchar(200);

Verify and Prevent

Insert the previously failing value and confirm success; verify the stored length equals the input.

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.