DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 01004 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 01004: String Data Right Truncation

PostgreSQL error code 01004 (string_data_right_truncation) occurs when you attempt to store a string value that exceeds the maximum length defined for a target column or variable. While the SQL standard treats this as a WARNING, PostgreSQL raises it as an error by default, immediately halting the operation. It most commonly appears with length-constrained types such as VARCHAR(n) and CHAR(n).

Top 3 Causes

1. Inserting or Updating Data That Exceeds Column Length

The most frequent cause: you defined a column as VARCHAR(n) and are trying to insert a string longer than n characters.

-- This will raise ERROR 22001 (the error-level sibling of 01004)
CREATE TABLE employees (
    emp_name VARCHAR(10)
);

-- Fails: 'Christopher' is 11 characters
INSERT INTO employees (emp_name) VALUES ('Christopher');

-- Fix: Increase the column size
ALTER TABLE employees ALTER COLUMN emp_name TYPE VARCHAR(100);

-- Or explicitly truncate with LEFT()
INSERT INTO employees (emp_name)
VALUES (LEFT('Christopher', 10));
Enter fullscreen mode Exit fullscreen mode

2. PL/pgSQL Function Variable Type Mismatch

Inside stored functions or procedures, assigning a long string to a short VARCHAR(n) variable causes this error. It can be tricky to debug because the error points to the function, not the calling query.

-- Problematic function
CREATE OR REPLACE FUNCTION bad_example(p_input TEXT)
RETURNS VOID AS $$
DECLARE
    v_name VARCHAR(5);  -- Too short!
BEGIN
    v_name := p_input;  -- Fails if p_input > 5 chars
    RAISE NOTICE 'Name: %', v_name;
END;
$$ LANGUAGE plpgsql;

-- Fixed version using TEXT or a larger VARCHAR
CREATE OR REPLACE FUNCTION good_example(p_input TEXT)
RETURNS VOID AS $$
DECLARE
    v_name TEXT;  -- No length restriction
BEGIN
    v_name := LEFT(p_input, 50);  -- Trim to target column size
    RAISE NOTICE 'Name: %', v_name;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

3. Bulk Data Loading via COPY or ETL Pipelines

When loading data from CSV files or external systems using COPY, source data often contains strings longer than the target column allows, causing batch failures.

-- Step 1: Load everything into a staging table with TEXT columns
CREATE TEMP TABLE staging_employees (
    emp_name TEXT,
    department TEXT,
    email TEXT
);

COPY staging_employees
FROM '/data/employees.csv'
WITH (FORMAT csv, HEADER true);

-- Step 2: Check for overflow before inserting into the real table
SELECT emp_name, LENGTH(emp_name) AS name_length
FROM staging_employees
WHERE LENGTH(emp_name) > 10;

-- Step 3: Insert with safe truncation
INSERT INTO employees (emp_name, department, email)
SELECT
    LEFT(TRIM(emp_name), 10),
    LEFT(TRIM(department), 50),
    LEFT(TRIM(email), 100)
FROM staging_employees;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario Fix
Column too short ALTER TABLE ... ALTER COLUMN ... TYPE VARCHAR(n)
One-off truncation needed LEFT(value, n) or SUBSTRING(value, 1, n)
PL/pgSQL variable Change to TEXT type
Bulk load failure Use a TEXT-typed staging table first 
-- Universal safe-insert pattern
INSERT INTO target_table (short_col)
VALUES (LEFT(:input_value, 50));  -- Always trim to column length
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Use TEXT unless there is a strict business rule requiring a length limit.
PostgreSQL's TEXT type has identical internal storage to VARCHAR with no performance penalty. Avoid premature length constraints.

-- Prefer this
CREATE TABLE articles (
    title   TEXT,           -- No arbitrary limit
    slug    VARCHAR(100),   -- Enforced by URL design rules
    content TEXT
);
Enter fullscreen mode Exit fullscreen mode

Validate data length at the application layer AND enforce with database constraints.
Never rely on a single validation point. Add a CHECK constraint as a safety net.

-- Database-level safety net
ALTER TABLE employees
ADD CONSTRAINT chk_emp_name_length
CHECK (LENGTH(emp_name) <= 100);

-- Pre-migration audit query
SELECT
    MAX(LENGTH(emp_name))   AS max_name_len,
    COUNT(*) FILTER (WHERE LENGTH(emp_name) > 100) AS overflow_rows
FROM source_data;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • 22001 – The ERROR-level version of this warning; most commonly seen in practice and causes immediate transaction rollback.
  • 22000 – General data_exception parent class.
  • 23514 – check_violation, triggered when a CHECK constraint on string length is violated.

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

Top comments (0)