How to fix String data right truncation in PostgreSQL

PostgreSQLBEGINNERMEDIUMUpdated Dec 28, 2025· 310 views

Error 22001 occurs when you try to insert or update a string value that exceeds the defined column length (e.g., a 15-character string in a VARCHAR(10) column). PostgreSQL rejects the data to protect data integrity, and you must either shorten the data or expand the column.

Quick fix

Fixes 22001

INSERT INTO users (name, email) VALUES ('John Doe', 'verylongemailthatexceedsthecolumnlength@example.com');
-- ERROR: value too long for type character varying(50)
  1. Identify the failing column using the error message
  2. Check the table schema to confirm column lengths
  3. Measure the data you are trying to insert
  4. Truncate the data to fit the column (if acceptable)
postgresql — error
INSERT INTO users (name, email) VALUES ('John Doe', 'verylongemailthatexceedsthecolumnlength@example.com');
-- ERROR: value too long for type character varying(50)
String data right truncation

What this error means

Error 22001 "String data right truncation" (SQLSTATE 22001) occurs when PostgreSQL encounters a string value that is too long for the target column's defined size. Unlike some databases that silently truncate long strings, PostgreSQL enforces strict data validation and refuses to insert or update values that exceed the declared column length. This error applies to fixed-length CHAR columns and VARCHAR columns with explicit length constraints. PostgreSQL prioritizes data integrity over convenience—it will not silently cut your data. If you explicitly cast a value to a truncated varchar length (e.g., 'too long'::varchar(5)), PostgreSQL will truncate it without error. However, in normal INSERT or UPDATE operations without explicit casting, exceeding the column length triggers this error. The error signals a mismatch between your application's data and your database schema constraints.

Typical symptoms

  • INSERT or UPDATE statement fails with error 22001
  • Application receives error message "value too long for type character varying(n)"
  • Specific column rejects data, while other columns in the same table accept it
  • Error occurs unexpectedly after adding a new column with a length constraint
  • Data that previously worked in another database system now fails in PostgreSQL

Common causes

  1. Inserting a string longer than the VARCHAR(n) column length definition
  2. Updating a column with a string that exceeds its maximum allowed length
  3. Changing column definition from TEXT or unlimited VARCHAR to a length-constrained VARCHAR
  4. Multibyte Unicode characters that consume more bytes than character count suggests
  5. Data import or migration from another database system with less strict validation
  6. Application logic not validating input string length before database operations

How to fix "String data right truncation"

1Identify the failing column using the error message

PostgreSQL provides detailed error context. Run your failing query directly in psql to see the full error:

sql
INSERT INTO users (name, email) VALUES ('John Doe', 'verylongemailthatexceedsthecolumnlength@example.com');
-- ERROR: value too long for type character varying(50)

The error message indicates which column caused the issue. Note the column name and the specified length (in this example, VARCHAR(50) for email).

2Check the table schema to confirm column lengths

Use the \d command in psql to inspect the table definition:

sql
\d users

This displays the table structure, showing all column names, data types, and constraints. Look at the character-type columns (CHAR, VARCHAR) and note their length limits. For example:

bash
Column |          Type          | Collation | Nullable | Default
--------|------------------------|-----------|----------|--------
id     | integer                |           | not null |
name   | character varying(100) |           | not null |
email  | character varying(50)  |           | not null |
3Measure the data you are trying to insert

Before attempting the insert again, check the length of your data:

sql
-- In PostgreSQL, use the length() function
SELECT length('verylongemailthatexceedsthecolumnlength@example.com');  -- Returns character count
SELECT octet_length('verylongemailthatexceedsthecolumnlength@example.com');  -- Returns byte count

If your string is 65 characters but the column is VARCHAR(50), you must either truncate the data or expand the column. Compare your actual data length against the column's maximum length limit.

4Truncate the data to fit the column (if acceptable)

If losing trailing characters is acceptable, truncate the string in your INSERT or UPDATE statement:

sql
-- Using explicit casting to truncate
INSERT INTO users (name, email) VALUES (
  'John Doe',
  'verylongemailthatexceedsthecolumnlength@example.com'::varchar(50)
);

-- Using SUBSTRING to truncate
INSERT INTO users (name, email) VALUES (
  'John Doe',
  SUBSTRING('verylongemailthatexceedsthecolumnlength@example.com', 1, 50)
);

-- Using LEFT function to truncate
INSERT INTO users (name, email) VALUES (
  'John Doe',
  LEFT('verylongemailthatexceedsthecolumnlength@example.com', 50)
);

Note: Explicit casting (::varchar(n)) is the PostgreSQL standard way to explicitly truncate strings without raising an error.

5Increase the column length if truncation is not acceptable

If the data is important and cannot be truncated, alter the column to accept longer values:

sql
-- Increase VARCHAR length
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(100);

-- Change to TEXT for unlimited length (no performance penalty in PostgreSQL)
ALTER TABLE users ALTER COLUMN email TYPE TEXT;

Important: In PostgreSQL 12 and later, increasing VARCHAR length is a fast metadata-only operation and does not rewrite the table. You can safely run this on production without extended downtime. However, be cautious when reducing VARCHAR length, as PostgreSQL will validate that all existing data fits the new constraint before allowing the change.

6Add validation in your application layer

Prevent this error from recurring by validating input length before database operations:

javascript
// JavaScript/Node.js example
const email = userInput.email;
const MAX_EMAIL_LENGTH = 50;

if (email.length > MAX_EMAIL_LENGTH) {
  throw new Error(`Email must be ${MAX_EMAIL_LENGTH} characters or less`);
}

// Proceed with insert
await db.insert({ email });

This catches the error at the application layer before it reaches the database, providing faster feedback to users and reducing database errors.

Advanced notes

Multibyte Characters and Encoding: PostgreSQL counts characters, not bytes, when enforcing VARCHAR length limits. However, some character sets (like UTF-8) use multiple bytes per character. A column defined as VARCHAR(50) accepts 50 characters regardless of how many bytes they consume. This can matter if you're migrating from a system that counts bytes instead of characters.

Unicode Normalization: Some Unicode characters can be represented in different ways (e.g., é as a single character or as e + combining accent). If you're experiencing unexpected length issues with Unicode data, consider using functions like unicode_normalize() to ensure consistent representation.

Silent Truncation with CHAR: Columns defined as CHAR(n) (fixed-length) instead of VARCHAR(n) (variable-length) silently truncate excess spaces at the right, but overflow characters still raise error 22001. Use VARCHAR when you need variable-length storage.

Using TEXT Type: PostgreSQL TEXT columns have the same performance as VARCHAR—there is no penalty for using TEXT instead of VARCHAR(n). If you find yourself frequently hitting this error, consider migrating appropriate columns to TEXT and implementing application-level validation instead.

Bulk Data Imports: When using COPY or importing data, even a single row with an oversized column will reject the entire batch. Use the FORCE_QUOTE and delimiter options carefully, and consider pre-processing data with tools like awk or Python before import.

Error Details from Different Tools: Different PostgreSQL client libraries may report this error with slightly different SQLSTATE codes (22001 is standard SQL, but some older systems used 1406). Always check your specific PostgreSQL version's documentation if you encounter variations.

Frequently asked questions

What does "22001" mean?
Error 22001 "String data right truncation" (SQLSTATE 22001) occurs when PostgreSQL encounters a string value that is too long for the target column's defined size. Unlike some databases that silently truncate long strings, PostgreSQL enforces strict data validation and refuses to insert or update values that exceed the declared column length. This error applies to fixed-length CHAR columns and VARCHAR columns with explicit length constraints.
How do I fix "22001"?
Error 22001 occurs when you try to insert or update a string value that exceeds the defined column length (e.g., a 15-character string in a VARCHAR(10) column). PostgreSQL rejects the data to protect data integrity, and you must either shorten the data or expand the column.
What causes "22001"?
Inserting a string longer than the VARCHAR(n) column length definition Updating a column with a string that exceeds its maximum allowed length Changing column definition from TEXT or unlimited VARCHAR to a length-constrained VARCHAR
Is the "22001" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading