PostgreSQL 22001 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22001: String Data Right Truncation

PostgreSQL error code 22001 (string data right truncation) occurs when you attempt to insert or update a string value that exceeds the maximum length defined for a VARCHAR(n) or CHAR(n) column. Unlike MySQL, which may silently truncate data depending on its sql_mode, PostgreSQL enforces strict type checking and raises this error to protect data integrity. You'll most commonly encounter this in production when user input is longer than expected or during data migrations from other databases.

Top 3 Causes

1. Column Length Too Small for Actual Data

The column was defined with a length that no longer accommodates real-world data as the application grows.

-- This will throw ERROR 22001 if username exceeds 10 chars
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(10)
);

INSERT INTO users (username) VALUES ('this_is_a_very_long_username');
-- ERROR:  value too long for type character varying(10)

-- Fix: Alter the column to a larger size (no table rewrite needed for increases)
ALTER TABLE users
    ALTER COLUMN username TYPE VARCHAR(100);

-- Or use TEXT if there's no strict business rule on length
ALTER TABLE users
    ALTER COLUMN username TYPE TEXT;

2. Data Migration from Other Databases

Source databases like MySQL may have silently truncated or allowed longer strings, causing failures when the same data is loaded into PostgreSQL.

-- Detect oversized data before migration
SELECT id, username, LENGTH(username) AS len
FROM source_users
WHERE LENGTH(username) > 20
ORDER BY len DESC;

-- Safe insert with explicit truncation during migration
INSERT INTO users (id, username)
SELECT id, LEFT(username, 20)
FROM source_users
WHERE LENGTH(username) > 20;

-- Always back up originals first
CREATE TABLE users_migration_backup AS
SELECT * FROM source_users WHERE LENGTH(username) > 20;

3. Missing Application-Level Validation

User input is passed directly to the database without length validation, bypassing any frontend maxlength constraints via direct API calls or automated scripts.

-- Add a CHECK constraint as a database-level safety net
ALTER TABLE users
    ADD CONSTRAINT chk_username_length
    CHECK (LENGTH(username) <= 100);

-- Query to find all VARCHAR columns and their limits in a table
SELECT
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
WHERE c.relname = 'users'
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND a.atttypmod > 0;

Quick Fix Solutions

-- 1. Increase column length (safest, online operation for increases)
ALTER TABLE your_table ALTER COLUMN your_column TYPE VARCHAR(255);

-- 2. Remove length restriction entirely
ALTER TABLE your_table ALTER COLUMN your_column TYPE TEXT;

-- 3. Identify which rows are causing the problem
SELECT id, your_column, LENGTH(your_column) AS actual_len
FROM your_table
WHERE LENGTH(your_column) > 50;  -- replace 50 with your column limit

Prevention Tips

1. Prefer TEXT over VARCHAR(n) when there's no strict business rule.
In PostgreSQL, TEXT and VARCHAR share the same internal storage — there is zero performance difference. Reserve VARCHAR(n) only for fields with genuine length constraints (e.g., postal codes, phone numbers). This eliminates the entire class of 22001 errors for general-purpose string fields.

2. Validate column lengths in your CI/CD pipeline.
Read character_maximum_length from information_schema.columns in your integration tests and assert that all test inputs respect those boundaries. Catching this at the pipeline level — before deployment — is far cheaper than debugging a production incident at 3 AM.

-- Use this in your test suite to dynamically fetch column limits
SELECT column_name, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'users'
  AND character_maximum_length IS NOT NULL;

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community