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.
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.
PostgreSQL provides detailed error context. Run your failing query directly in psql to see the full error:
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
-- 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).
Use the \d command in psql to inspect the table definition:
\d usersThis 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:
Column | Type | Collation | Nullable | Default
--------|------------------------|-----------|----------|--------
id | integer | | not null |
name | character varying(100) | | not null |
email | character varying(50) | | not null |Before attempting the insert again, check the length of your data:
-- In PostgreSQL, use the length() function
SELECT length('[email protected]'); -- Returns character count
SELECT octet_length('[email protected]'); -- Returns byte countIf 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.
If losing trailing characters is acceptable, truncate the string in your INSERT or UPDATE statement:
-- Using explicit casting to truncate
INSERT INTO users (name, email) VALUES (
'John Doe',
'[email protected]'::varchar(50)
);
-- Using SUBSTRING to truncate
INSERT INTO users (name, email) VALUES (
'John Doe',
SUBSTRING('[email protected]', 1, 50)
);
-- Using LEFT function to truncate
INSERT INTO users (name, email) VALUES (
'John Doe',
LEFT('[email protected]', 50)
);Note: Explicit casting (::varchar(n)) is the PostgreSQL standard way to explicitly truncate strings without raising an error.
If the data is important and cannot be truncated, alter the column to accept longer values:
-- 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.
Prevent this error from recurring by validating input length before database operations:
// 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.
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.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL