PostgreSQL 22001 Error: Causes and Solutions Complete Guide

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 column. Unlike some other databases that silently truncate data, PostgreSQL strictly enforces column length constraints and raises this error to protect data integrity. This error is part of the data_exception (22000) error class and will immediately abort the current transaction.

---

Top 3 Causes

1. Inserting Data Longer Than Column Definition

The most common cause is simply trying to store a string that is longer than the column's defined limit.

-- This will fail if full_name is VARCHAR(20)
INSERT INTO users (full_name, email)
VALUES ('Alexander Christopher Johnson', '[e-mail]');

-- ERROR: value too long for type character varying(20)

-- Fix: Check the column definition first
SELECT column_name, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'full_name';

-- Fix: Alter the column to accommodate larger data
ALTER TABLE users
  ALTER COLUMN full_name TYPE VARCHAR(100);

2. Schema Not Updated After Business Requirement Changes

As applications evolve, original column size assumptions often become outdated. For example, a phone number column designed for domestic numbers may need to support international formats.

-- Original column too small for international numbers
-- phone_number VARCHAR(11) can't store '[phone]'

-- Check current problematic data
SELECT id, phone_number, LENGTH(phone_number) AS len
FROM customers
WHERE LENGTH(phone_number) > 11
ORDER BY len DESC;

-- Fix: Expand the column size safely
BEGIN;
ALTER TABLE customers
  ALTER COLUMN phone_number TYPE VARCHAR(20);
COMMIT;

-- Or remove the length restriction entirely using TEXT
ALTER TABLE products
  ALTER COLUMN description TYPE TEXT;

3. Data Migration or ETL Mismatches

When migrating data from another database system (e.g., MySQL, Oracle), column size definitions may differ, causing unexpected truncation errors on bulk loads.

-- Pre-migration validation: detect oversized data before loading
SELECT
    id,
    full_name,
    LENGTH(full_name) AS actual_length,
    20 AS target_column_limit
FROM staging_users
WHERE LENGTH(full_name) > 20
ORDER BY actual_length DESC;

-- Summary report before migration
SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN LENGTH(full_name) > 20 THEN 1 ELSE 0 END) AS rows_exceeding_limit,
    MAX(LENGTH(full_name)) AS max_length_found
FROM staging_users;

-- Handle the error gracefully using PL/pgSQL exception handling
DO $$
DECLARE
    v_name TEXT := 'This is an extremely long name that exceeds our limit';
BEGIN
    INSERT INTO users (full_name) VALUES (v_name::VARCHAR(20));
EXCEPTION
    WHEN string_data_right_truncation THEN
        RAISE NOTICE 'Truncation detected for value: %, length: %',
            v_name, LENGTH(v_name);
END;
$$;

---

Quick Fix Solutions

-- Option 1: Extend the column length (no data loss)
ALTER TABLE your_table
  ALTER COLUMN your_column TYPE VARCHAR(255);

-- Option 2: Convert to TEXT type (no length restriction)
ALTER TABLE your_table
  ALTER COLUMN your_column TYPE TEXT;

-- Option 3: Explicitly truncate before insert (use with caution)
INSERT INTO users (full_name)
VALUES (LEFT('Very long name that needs trimming', 20));

-- Option 4: Filter out oversized rows during migration
INSERT INTO users (full_name, email)
SELECT full_name, email
FROM staging_users
WHERE LENGTH(full_name) <= 20;

---

Prevention Tips

Use TEXT type when no business rule requires a length limit.
In PostgreSQL, TEXT and VARCHAR use identical internal storage, so there is no performance difference. If there is no meaningful business constraint on a string column's length, default to TEXT to avoid this error entirely.

Validate data length at both the application and database layers.
Do not rely solely on application-side validation. Add CHECK constraints or triggers at the database level so that all data entry paths — including direct SQL, ORMs, and batch jobs — are consistently enforced.

-- Add a CHECK constraint as an additional safety net
ALTER TABLE products
  ADD CONSTRAINT chk_product_name_length
  CHECK (LENGTH(product_name) <= 200);

-- Use a reusable domain type for consistent enforcement
CREATE DOMAIN short_label AS VARCHAR(50)
  CHECK (VALUE IS NOT NULL AND LENGTH(TRIM(VALUE)) > 0);

---

Related Errors

• 22000 (data_exception): Parent error class for 22001 and all data-related exceptions.
• 22P02 (invalid_text_representation): Raised when an invalid text format is cast to another type; often appears alongside 22001 during migrations.
• 23514 (check_violation): Triggered when a CHECK constraint is violated, which may occur if length limits are enforced via CHECK rather than column type definitions.

---

📖 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.