"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):
ncounts characters, not bytes.ā,žor an emoji each count as one character, even though they take several bytes in UTF-8.- A longer string raises an error, unless the excess characters are all spaces. Then the value is quietly cut to the limit. The SQL standard requires this.
- An explicit cast to
varchar(n)truncates without any error. The standard requires this too. varcharwithout a length accepts strings of any length.nitself can be at most 10,485,760, and any single value is limited to about 1 GB.- There is no performance difference between
text,varcharandvarchar(n), apart from a few CPU cycles to check the length.
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:
- Measuring existing data is a full scan.
max(char_length(col))over a billion rows reads the whole table. Run it on a replica, or estimate withTABLESAMPLEfirst. - The lock is the real risk, not the rewrite. Even a metadata-only ALTER waits behind long transactions and blocks everything queued after it. Use
lock_timeout, retries and a quiet window. - Partitioned tables. A type change on the parent applies to all partitions. PostgreSQL does not let you change a column type on the parent alone. Plan for the lock on every partition.
- Logical replication. DDL is not replicated. Widen the column on the subscriber first, then on the publisher. Otherwise, in our experience, the first long value stops the apply worker.
- Many tenants or shards. The same column exists in hundreds of schemas or databases. Roll the change out with a script that records where it succeeded, and check that it ran everywhere.
- Indexes on long values. Removing a limit lets much longer strings into a B-tree index, and a B-tree entry has a maximum size of about a third of a page, after compression (B-tree indexes). If the column is indexed, keep a sensible CHECK limit, or index a hash of the value.
-- 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
- SQLSTATE 22001: a string exceeds a varchar(n) limit. n counts characters, not bytes.
- The message does not name the column: list columns with that limit, or use COPY's context line.
- Widening varchar or switching to text needs no rewrite since 9.2, but still takes ACCESS EXCLUSIVE: use lock_timeout.
- Shrinking a limit rewrites the table. Prefer text with a CHECK constraint added NOT VALID.
- Explicit ::varchar(n) casts truncate silently. Search your ETL code for them.
- For bulk loads, PostgreSQL 17+ COPY ON_ERROR ignore skips bad rows; log and fix them at the source.