PostgreSQL Warning: String Data Right Truncation (SQLSTATE 01004)

PostgreSQL raises WARNING: value was truncated to match type character varying(N) with SQLSTATE 01004 (condition name string_data_right_truncation) when a string value exceeds the declared length of a character type column and is silently shortened to fit. Unlike the closely related error 22001, which aborts the operation, 01004 is a warning-class condition — the operation succeeds, but the stored value may differ from what was supplied.

What This Error Means

SQLSTATE 01004 belongs to SQLSTATE class 01, the "warning" class. In the SQL standard, class 01 conditions are informational — they do not cause a transaction to fail or a statement to roll back. PostgreSQL surfaces this warning specifically when a string is truncated to fit a character(n) or character varying(n) column during certain cast or assignment operations.

The most common trigger is an explicit cast of an over-length string to a character type, such as CAST('abcdef' AS varchar(3)). In this case PostgreSQL silently returns 'abc' and emits the warning. By contrast, if you attempt to INSERT or UPDATE a row and a string value exceeds the declared column length, PostgreSQL raises the harder error 22001 (string_data_right_truncation in class 22), which aborts the statement.

This distinction is important: 01004 appears in contexts where PostgreSQL considers truncation acceptable by the SQL standard (explicit casts and certain function contexts), while 22001 appears when inserting data into a table column. Client applications receiving 01004 should treat it as a data-integrity signal — the value that was stored is not the value that was submitted.

Common Causes

1. Explicit casts to a shorter character varying(n) or char(n) type. Expressions like CAST(some_text AS varchar(10)) or the shorthand some_text::varchar(10) silently truncate if some_text is longer than 10 characters and emit 01004.

2. Assigning a long string to a character(n) column through a PL/pgSQL variable or function. Inside a PL/pgSQL function, assigning an over-length string to a char(n) or varchar(n) variable triggers the warning rather than an error, because the assignment is treated as an implicit cast.

3. Using to_char() or other formatting functions whose result is cast to a fixed-length type downstream. If the output of a formatting expression is then assigned to a typed variable or cast to a shorter type, the warning may appear.

4. ORM or migration tooling that issues explicit CAST expressions. Some ORMs generate SQL that explicitly casts values when binding parameters to typed columns, which can produce this warning in logs even when the actual stored data looks correct.

How to Fix string_data_right_truncation

1. Widen the target type. If truncation is not acceptable, increase the length of the varchar(n) or char(n) type to accommodate the longest expected value:

   ALTER TABLE my_table ALTER COLUMN my_col TYPE varchar(255);
   

2. Avoid unnecessary explicit casts. Remove casts that shorten the value, or cast to an unconstrained text type instead:

   -- Instead of this (may truncate):
   SELECT description::varchar(50) FROM products;
   
   -- Use text or a wider type:
   SELECT description::text FROM products;
   

3. Validate string length before casting. If you need to produce a fixed-width output, use substring() explicitly so the truncation is intentional and visible in the code:

   SELECT substring(description, 1, 50) AS short_description FROM products;
   

4. In PL/pgSQL, use text for intermediate variables. Declare local variables as text and only coerce to the narrower type at the final assignment point, where you can add an explicit check:

   DECLARE
     v_label text;
   BEGIN
     v_label := some_function_returning_long_string();
     IF length(v_label) > 20 THEN
       RAISE EXCEPTION 'label too long: %', v_label;
     END IF;
     INSERT INTO my_table(label) VALUES (v_label);
   END;
   

5. Promote warnings to errors in strict contexts. PostgreSQL does not have a built-in session variable to convert 01004 into an error, but you can check for warnings in your application layer after each statement and treat them as failures when data integrity matters.

Additional Information

• SQLSTATE class 01 is the SQL-standard warning class. Other members include 01000 (general warning), 01003 (null_value_eliminated_in_set_function), and 01007 (privilege_not_granted). None of these abort a transaction.
• The error-class sibling 22001 (string_data_right_truncation in class 22) is a proper error that aborts the statement and is raised on INSERT/UPDATE when a value is too long for a table column. If you are seeing 22001 instead of 01004, see the related article on that condition.
• Most PostgreSQL client drivers (libpq, psycopg2, asyncpg, JDBC) surface 01004 as a warning notice rather than raising an exception, so it can be missed unless your application explicitly handles notice callbacks.
• In psycopg2, you can inspect connection.notices after a query to detect truncation warnings. In asyncpg, add a listener via connection.add_log_listener().
• PostgreSQL's character(n) type (blank-padded fixed-length) is often a source of accidental truncation because it silently pads and truncates; text or varchar without a length constraint is usually preferable unless you need fixed-width storage for a specific reason.

Frequently Asked Questions

Why does my INSERT not raise 01004 but a CAST does? PostgreSQL treats explicit casts as a request for conversion, and the SQL standard permits truncation in that context (hence a warning, not an error). For INSERT/UPDATE into a table column, PostgreSQL enforces the column constraint strictly and raises SQLSTATE 22001 (an error) instead.

Will 01004 roll back my transaction? No. SQLSTATE class 01 warnings do not abort a statement or roll back a transaction. The operation completes and the truncated value is stored or returned. You need to detect the warning in your application code if you want to treat it as a failure.

How can I see these warnings in psql? Warnings emitted during a session are printed to the psql terminal by default. You should see a line like WARNING: value was truncated to match type character varying(10) immediately after the offending statement. In application code, you must register a notice handler with your driver.

Is this a PostgreSQL-specific behavior or SQL standard? The behavior is defined by the SQL standard: explicit casts to narrower character types are permitted to truncate with a warning. PostgreSQL follows this standard. MySQL and SQLite also truncate silently in many cases (sometimes without any warning at all), so this is a cross-database data-integrity concern, not a PostgreSQL quirk.