PostgreSQL Error 22001

SQLSTATE

22001

Condition name

string_data_right_truncation

Product

PostgreSQL

Versions

PostgreSQL 13 to 18 (examples run on PostgreSQL 18.6 in PGlite)

Category

Data Exceptions

Last reviewed

24 October 2026

What it means

SQLSTATE 22001 means a string is longer than the declared length of a varchar(n) or char(n) column or variable, so PostgreSQL rejected it instead of cutting it short.

A column declared as varchar(n) (character varying) or char(n) (character) can hold at most n characters; the limit counts characters, not bytes. Storing a longer string with INSERT, UPDATE or COPY, or assigning it to such a variable in PL/pgSQL, raises 22001 (string_data_right_truncation). The message gives only the type and length, not the column name, so with several columns of the same length you have to compare the values yourself; in COPY, the CONTEXT line names the line and column.

Two exceptions come from the SQL standard and can be surprising. If the extra characters are all spaces, the string is silently truncated to the maximum length. And an explicit cast, 'ABCD'::varchar(3), truncates to n characters without an error. Changing a column to a shorter length with ALTER TABLE fails with the same error when existing rows are too long.

Common causes

• The column was sized for typical data (varchar(50) for names, varchar(3) for codes) and real data is longer.
• An import or INSERT ... SELECT copies from a staging table of text columns without checking lengths.
• An UPDATE appends to a value (code || 'X'), pushing it past the limit.
• A char(2) country column receives a three-letter code, or a code column receives a full name.
• ALTER TABLE ... ALTER COLUMN ... TYPE varchar(n) shortens a column that already holds longer values.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'users'
  AND character_maximum_length IS NOT NULL
ORDER BY ordinal_position;

SELECT code, char_length(code) AS chars
FROM staging_users
WHERE char_length(code) > 3
ORDER BY chars DESC;

SELECT max(char_length(note)) AS longest_note
FROM users;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

ALTER TABLE users
    ALTER COLUMN code TYPE varchar(10);

INSERT INTO users (code)
SELECT code
FROM staging_users
WHERE char_length(code) <= 3;

INSERT INTO users (note)
SELECT left(note, 10)
FROM staging_notes;

Examples

A four-character code into varchar(3)

CREATE TABLE users (id int, code varchar(3));

INSERT INTO users (code) VALUES ('ABCD');

ERROR:  value too long for type character varying(3)

A three-letter country code into char(2)

CREATE TABLE users (id int, country char(2));

INSERT INTO users (country) VALUES ('GBR');

ERROR:  value too long for type character(2)

Loading from a staging table

The whole INSERT fails, including the row that fits, and the message does not say which row was too long. The read-only check above finds it.

CREATE TABLE users (id int, code varchar(3));
CREATE TABLE staging_users (code text);
INSERT INTO staging_users VALUES ('AB'), ('ABCDE');

INSERT INTO users (code) SELECT code FROM staging_users;

ERROR:  value too long for type character varying(3)

Shortening a column that holds longer values

CREATE TABLE users (id int, code varchar(3));
INSERT INTO users (code) VALUES ('ABC');

ALTER TABLE users ALTER COLUMN code TYPE varchar(2);

ERROR:  value too long for type character varying(2)

How to prevent it

Give columns a length limit only when there is a real rule behind it (an ISO code, a fixed format); otherwise use text. Validate lengths in the application with the same limits as the database, counting characters rather than bytes.

For imports, load into text staging columns, check char_length against the target limits, and report rows that do not fit instead of letting one row fail the whole load.

Common questions

Learn more on CodeWithSQL

• SQL data types explained
• LENGTH function

Official documentation

• PostgreSQL error codes (Appendix A)
• Character types
• ALTER TABLE
• COPY

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.