PostgreSQL 22001: value too long for type
A string is longer than the column's varchar(n) or char(n) limit. PostgreSQL rejects it rather than silently truncating.
- SQLSTATE
22001- Name
string_data_right_truncation- Category
- Data Exception
What it means
A varchar(n) or char(n) column holds at most n characters. Inserting or updating a longer value fails with 22001 (string_data_right_truncation). PostgreSQL only truncates silently when you cast explicitly, as in 'abcdef'::varchar(3), which the SQL standard requires.
The message names the type but not the column, so with several limited columns you need to check which value is too long.
Common causes
- User input or imported data longer than the column definition.
- A multi-column
INSERTwhere values are in a different order than the column list, putting a long value in a short column. - A schema migrated from another database with tight limits.
How to fix it
-
Find which column overflowed by comparing value lengths with the limits.
SELECT column_name, character_maximum_length FROM information_schema.columns WHERE table_name = 'customers' AND character_maximum_length IS NOT NULL;
-
Widen the column, or use
text, which has no length limit and performs the same asvarchar.ALTER TABLE customers ALTER COLUMN name TYPE varchar(200); -- or ALTER TABLE customers ALTER COLUMN name TYPE text;
-
If truncation is acceptable, do it explicitly.
INSERT INTO customers (name) VALUES (left('a very long name ...', 20));
Reproduce it safely
SELECT CAST('abc' AS varchar(2)) AS silently_truncated;
CREATE TEMP TABLE t (code varchar(2));
INSERT INTO t VALUES ('abc'); -- ERROR: value too long for type character varying(2)
Questions
Is text slower than varchar in PostgreSQL?
No. text and varchar use the same storage. A length limit is a constraint on the data, not a performance feature.
Related errors
22003numeric value out of range22P02invalid input syntax for type8152SQL Server: String or binary data truncated2628SQL Server: Truncated in table, column1406MySQL: Data too long for columnORA-12899Oracle: value too large for column
More data exception errors
22021invalid byte sequence for encoding22008date/time field value out of range22012division by zero22007invalid datetime format22023invalid parameter value
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.