PostgreSQL String Data Length Mismatch (SQLSTATE 22026)

PostgreSQL raises ERROR: string_data_length_mismatch with SQLSTATE 22026 when a value supplied to a fixed-length character column or parameter does not match the declared length exactly. This error belongs to error class 22 (Data Exception) and is most commonly encountered with the CHAR(n) (or CHARACTER(n)) type and certain external data sources.

What This Error Means

SQLSTATE class 22 covers data exception errors — conditions where a value is structurally valid SQL but violates a type or domain constraint at the data level. Code 22026 specifically signals that the length of a character string does not match what the type definition requires.

In PostgreSQL, CHAR(n) is a fixed-length type: values shorter than n characters are space-padded to exactly n characters, and values longer than n characters cause an error (SQLSTATE 22001, string_data_right_truncation). SQLSTATE 22026 covers the case where an exact-length match is required but the provided string has the wrong length — a situation that arises primarily when importing data via protocols or foreign data wrappers that enforce strict length semantics, or when using certain type input/cast paths that do not perform silent padding.

When this error occurs, the current statement is aborted and the transaction is placed in an aborted state. If you are in an explicit transaction block, you must issue ROLLBACK (or ROLLBACK TO SAVEPOINT) before issuing any further commands. The connection itself remains open.

Common Causes

1. Inserting a string into a CHAR(n) column via a strict external interface. Some foreign data wrappers, COPY pipelines, or binary protocol paths enforce exact-length semantics on CHAR(n) rather than applying the standard space-padding. A value with fewer or more characters than the declared width triggers 22026.

2. Type casting with explicit fixed-length character types. Expressions like CAST('AB' AS CHAR(5)) normally pad silently in PostgreSQL, but user-defined types, domain constraints, or custom cast functions that validate length strictly can raise this error.

3. Binary COPY or logical replication mismatches. When using COPY ... BINARY or replicating between databases with mismatched schema definitions, a CHAR(n) column on the source may not align with the target's CHAR(m) column, producing a length mismatch during decode.

4. PL/pgSQL or server-side function arguments declared as CHAR(n). Passing a string argument of the wrong length to a stored procedure or function whose parameter is typed as CHAR(n) can trigger this error in strict cast contexts.

How to Fix string_data_length_mismatch

1. Pad or trim the value explicitly before inserting. Ensure the string is exactly the required length using rpad or lpad:

   -- Insert a value padded to exactly 5 characters
   INSERT INTO my_table (code) VALUES (rpad('AB', 5));
   
   -- Or trim to fit
   INSERT INTO my_table (code) VALUES (substring('ABCDEFG' FROM 1 FOR 5));
   

2. Reconsider whether CHAR(n) is the right type. In most PostgreSQL applications, VARCHAR(n) or plain TEXT is preferable. CHAR(n) provides no performance benefit in PostgreSQL and its padding semantics cause subtle bugs. If the column does not require strict fixed-length enforcement, alter it:

   ALTER TABLE my_table ALTER COLUMN code TYPE VARCHAR(5);
   -- or for no length restriction:
   ALTER TABLE my_table ALTER COLUMN code TYPE TEXT;
   

3. Fix schema mismatches in COPY or replication pipelines. When the error occurs during COPY ... BINARY or logical replication, inspect both the source and target column definitions and ensure they match exactly:

   -- Check current column type
   SELECT column_name, data_type, character_maximum_length
   FROM information_schema.columns
   WHERE table_name = 'my_table' AND column_name = 'code';
   

4. Adjust the value transformation in your ETL or application code. If the data originates from an external system (CSV, API, foreign table), normalize string lengths before passing them to PostgreSQL rather than relying on the database to silently adjust them.

5. Wrap the operation in a savepoint for partial-failure recovery. If this error can occur intermittently during bulk loads, use savepoints so one bad row does not abort an entire transaction:

   BEGIN;
   SAVEPOINT before_insert;
   INSERT INTO my_table (code) VALUES ('AB');  -- may raise 22026
   -- On error, issue: ROLLBACK TO SAVEPOINT before_insert;
   COMMIT;
   

Additional Information

• SQLSTATE 22026 is defined in the SQL standard and is not PostgreSQL-specific, but PostgreSQL rarely raises it for normal CHAR(n) inserts because it applies space-padding automatically. The error is most visible at the binary protocol level or via non-standard cast paths.
• Related SQLSTATE codes in class 22:
  • 22001 — string_data_right_truncation: value too long for the column (more common).
  • 22000 — data_exception: generic data exception fallback.
  • 2200F — zero_length_character_string: an empty string where a non-empty one is required.
• Most PostgreSQL client drivers (libpq, psycopg2, JDBC) surface this as a PSQLException or DataException with the SQLSTATE 22026 attached. Check the getSQLState() / pgcode attribute to distinguish it from other data errors.
• ORMs such as SQLAlchemy and ActiveRecord typically map CHAR(n) to fixed-length string types; they do not pad values on the client side, so application-level strings that are shorter than n characters can trigger this error through the binary protocol path used by some drivers.

Frequently Asked Questions

Why does PostgreSQL pad CHAR(n) in some cases but raise an error in others? Standard SQL specifies that CHAR(n) values should be space-padded to n characters on input. PostgreSQL follows this for text-protocol inserts and most cast operations. However, certain code paths — particularly the binary COPY format, some foreign data wrappers, and strict cast functions — enforce exact-length semantics and do not apply padding, which is when 22026 is raised.

Is 22026 the same as 22001 (string_data_right_truncation)? No. 22001 is raised when a string is too long for the destination column or type. 22026 indicates a length that does not match what is expected — which in practice means the value is the wrong length for a fixed-length type, often in a context where padding is not applied automatically. 22001 is far more common in everyday PostgreSQL use.

Should I ever use CHAR(n) in PostgreSQL? Rarely. Unlike some other databases, PostgreSQL stores CHAR(n) and VARCHAR(n) identically under the hood — there is no storage or performance advantage to CHAR(n). Its space-padding behaviour can mask bugs, and it can produce subtle comparison anomalies. The PostgreSQL documentation itself recommends TEXT or VARCHAR for most use cases.

How do I identify which column or value is causing the mismatch? Check the PostgreSQL server log for the full error detail. The DETAIL line usually names the column involved. You can also increase log_min_error_statement to error in postgresql.conf to log the offending SQL statement, or wrap the insert in a PL/pgSQL block with an EXCEPTION handler to capture the context programmatically.