Incident Response

PostgreSQL "value too long for type character varying": How to Fix It

The error does not tell you which column failed, widening the column is cheaper than most people think, and an explicit cast can quietly cut your data. Here is how to find the column, fix it safely and stop it coming back.

· 15 min read · AG Data Team

"value too long for type character varying(n)" means you tried to store a string longer than the limit declared on a varchar(n) column. PostgreSQL rejects the whole statement with SQLSTATE 22001, string_data_right_truncation (error codes). The fix is usually simple. The two parts that cost time are finding which column failed and changing the limit on a big, busy table.

CREATE TABLE customers (id bigint, email varchar(50), city varchar(50));

INSERT INTO customers VALUES (1, 'a-very-long-address-from-a-marketing-import@example.com', 'Riga');
-- ERROR:  value too long for type character varying(50)

What PostgreSQL actually checks

The rules are short and worth knowing exactly (character types):

That last point matters for the fix. In PostgreSQL, varchar(255) is not faster or smaller than text. The limit is a business rule, nothing more.

Step 1: find the column

The message names the type and the limit, but not the column. You can see that in the source: the error is raised inside the varchar input and length-coercion functions, which do not know which column they are filling (varchar.c). On a table with five varchar(50) columns, the message alone will not tell you which one failed.

-- which columns of this table have exactly this limit?
SELECT column_name, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'customers'
  AND character_maximum_length = 50;

Then compare the values your application sent with that short list. Your driver or ORM log usually has the bound parameters. If the insert comes from a function or trigger, the column with that limit may be in a different table than the one you wrote to. Check the CONTEXT line of the error.

COPY is more helpful. Its error context includes the line number and the column name:

COPY customers FROM '/tmp/customers.csv' WITH (FORMAT csv);
-- ERROR:  value too long for type character varying(50)
-- CONTEXT:  COPY customers, line 18234, column email: "..."

Step 2: decide what the limit is for

Before you change anything, ask whether the limit means something. A country code in varchar(2) is a real rule, and a 30-character value there is bad data you should reject upstream. An email column in varchar(50) is a guess somebody made years ago. Real addresses are longer, and the error is now blocking legitimate customers.

In our experience, most of these incidents are the second kind: a default length from an ORM or a copied schema that real data finally outgrew.

Fix A: widen the column

SET lock_timeout = '3s';
ALTER TABLE customers ALTER COLUMN email TYPE varchar(320);
-- or remove the limit entirely:
ALTER TABLE customers ALTER COLUMN email TYPE text;

Widening a varchar limit, or removing it, has not required a table rewrite since PostgreSQL 9.2 (9.2 release notes). The planner treats the length change as a no-op when the new limit is equal or larger. Going from varchar to text doesn't rewrite the table either. The ALTER TABLE docs say indexes are not rebuilt for text ↔ varchar, because the two types sort identically (ALTER TABLE).

The catch is the lock. ALTER COLUMN TYPE takes an ACCESS EXCLUSIVE lock even when nothing is rewritten. If a long transaction holds any lock on the table, your ALTER waits, and every query behind it waits too. That's why the example sets lock_timeout: fail fast and retry, rather than freeze the table. The full pattern is in zero-downtime PostgreSQL migrations.

If a view uses the column, the ALTER fails with cannot alter type of a column used by a view or rule. You have to drop and recreate the view in the same transaction as the ALTER.

Shrinking a limit is a different operation. The no-rewrite shortcut only applies when the new limit is greater than or equal to the old one. Changing varchar(255) to varchar(100) rewrites the table and its indexes under ACCESS EXCLUSIVE. On a large table, that is an outage.

Fix B: text plus a CHECK constraint

If you need a limit you can change later without touching the type, store text and enforce the length with a constraint:

-- business limit as a constraint instead of a type modifier
ALTER TABLE customers ALTER COLUMN city TYPE text;   -- no rewrite, no index rebuild
ALTER TABLE customers
  ADD CONSTRAINT customers_city_len CHECK (char_length(city) <= 100) NOT VALID;
ALTER TABLE customers VALIDATE CONSTRAINT customers_city_len;  -- SHARE UPDATE EXCLUSIVE

With NOT VALID, new and updated rows are checked immediately, but existing rows are not scanned. VALIDATE CONSTRAINT then scans them under a SHARE UPDATE EXCLUSIVE lock, which does not block normal reads and writes (ALTER TABLE). To change the limit later, add a new constraint and drop the old one. The type stays the same, so no rewrite happens.

Fix C: truncate on purpose, not by accident

Sometimes cutting the value is the right answer: a free-text note, or a user agent string you store only for debugging. Then truncate explicitly and visibly, with left(value, 200) in the query or in application code, and keep a log of it.

Be careful with casts, though:

SELECT 'abcdefgh'::varchar(5);       -- 'abcde', no error, no warning
SELECT 'abc     '::varchar(5);       -- 'abc  ', trailing spaces are clipped silently
INSERT INTO t (code) VALUES ('abcdefgh');  -- varchar(5): ERROR

An ETL job that writes value::varchar(50) "to make the types match" never fails. It just cuts data, and nobody notices until a customer's address is missing its last line. When we review loading code, we search for casts to varchar(n) and char(n) for this reason.

Bulk loads: don't let one row kill the job

By default, one bad row aborts the whole COPY. Since PostgreSQL 17, ON_ERROR ignore skips rows whose values fail input conversion, and an over-length varchar is one of those failures (PostgreSQL 17 release notes). With LOG_VERBOSITY verbose, PostgreSQL emits a notice with the line and column of each skipped row (COPY).

-- PostgreSQL 17+: skip rows that fail input conversion and log them
COPY customers FROM '/tmp/customers.csv'
  WITH (FORMAT csv, ON_ERROR ignore, LOG_VERBOSITY verbose);

Newer versions add REJECT_LIMIT to fail after too many bad rows, and set_null to keep the row and null the field. Check the COPY page for your version before relying on either. Either way, collect the rejected rows and fix them at the source. Silently skipping customer data is a bug, not a feature.

Coming from MySQL

This error often first appears right after a migration. With strict SQL mode off, MySQL truncates an over-length VARCHAR value and only raises a warning (MySQL CHAR and VARCHAR). Applications built on such a server may have been losing data for years without noticing. On PostgreSQL the same insert fails, so the migration exposes a problem that was always there. Before cutover, check the maximum lengths in the source data and the sizes of strings your application sends. More on this in our MySQL to PostgreSQL migration guide.

On large tables and many databases

Everything above is easy on a test database with a thousand rows. In production, a few things change:

-- how close is existing data to the limit? (full scan: run on a replica)
SELECT max(char_length(email)) AS max_chars,
       max(octet_length(email)) AS max_bytes
FROM customers;

-- quick estimate on a huge table: read ~1% of pages
SELECT max(char_length(email)) FROM customers TABLESAMPLE SYSTEM (1);

Key takeaways

PostgreSQL Errors Data types Migrations
Work with us

Data loads failing on column limits?

We fix failing imports and migrations, change column types on large production tables without long locks, and review schemas before they cause incidents.

Get in touch More from the blog