📋

Description

Error 22001, 'string data right truncation', occurs when an application attempts to insert or update a string value that exceeds the defined maximum length of the target database column. This typically happens when the input data is longer than the column's `VARCHAR(n)` or `CHAR(n)` constraint, leading to potential data loss if not handled.

💬

Error Message

string data right truncation

🔍

Known Causes

4 known causes

⚠️

Input Data Exceeds Column Length

The most common cause is providing a string value that is longer than the maximum length defined for the target database column (e.g., trying to insert 'very long string' into a `VARCHAR(10)` column).

⚠️

Application-Database Mismatch

The application's data model or ORM might be configured with different string length expectations than the actual PostgreSQL column definitions, causing truncation attempts.

⚠️

Character Encoding Discrepancy

When dealing with multi-byte character sets, a string that appears short in characters might exceed the byte limit of a column if encoding differs between the client and server.

⚠️

Implicit Type Conversion

During an implicit data type conversion by PostgreSQL, the resulting string representation might exceed the target column's defined length, leading to truncation.

🛠️

Solutions

4 solutions available

1. Increase Column Size easy

Make column larger to fit data

1

Check current column size

SELECT column_name, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'name';

2

Increase VARCHAR length

ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(500);

3

Or use TEXT for unlimited length

ALTER TABLE users ALTER COLUMN name TYPE TEXT;

2. Truncate Data easy

Shorten data to fit column

1

Truncate in SQL

INSERT INTO users (name) VALUES (LEFT('Very long name...', 50));

2

Truncate in application

# Python
name = long_name[:50]

# JavaScript
const name = longName.substring(0, 50);

3. Validate Input Length easy

Check length before inserting

1

Add application validation

def validate_name(name, max_length=50):
    if len(name) > max_length:
        raise ValueError(f'Name exceeds {max_length} characters')
    return name

4. Use CHAR vs VARCHAR easy

Understand fixed vs variable length

1

CHAR pads to fixed length

-- CHAR(10) stores exactly 10 chars (padded with spaces)
-- VARCHAR(10) stores up to 10 chars (no padding)

-- For variable length data, use VARCHAR or TEXT
ALTER TABLE codes ALTER COLUMN code TYPE VARCHAR(20);

🔗

Related Errors

5 related errors

PostgreSQL Error 22001: String Data Truncation