value too long for type character varying

What it means

A column declared varchar(20) (the same type as character varying(20)) or char(2) holds at most that many characters, not bytes, so é and € count as one each. Your statement tried to store a longer string, and the whole statement was rejected.

The error names the type but not the column, and there’s no LINE pointer for an INSERT. With several columns of the same length you have to work out which one it was. COPY is the exception: its CONTEXT line names the column and the value.

Common causes

1. Real data longer than the guess made at design time: names, addresses, email addresses, URLs, user agents, product titles.
2. A different source: an import from another system, or a field the application started filling with longer values (a full name instead of a first name, a URL with tracking parameters).
3. The wrong value in the wrong column, often from an INSERT without a column list, so values land in a different order than you think.
4. char(n) used for codes that turned out to be longer, such as a 3-letter country code in a char(2) column.

How to fix it

Find the column

List the length-limited columns of the table and match the number in the message:

SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'customers'
  AND character_maximum_length IS NOT NULL
ORDER BY ordinal_position;

If the data is already in a staging table, find the offending rows:

SELECT id, length(name) FROM staging WHERE length(name) > 20;

On PostgreSQL 16 and later, pg_input_is_valid(name, 'varchar(20)') returns false for the same rows.

Widen the column, or make it text

Usually the limit is the thing that’s wrong. Raising it, or removing it, is cheap:

ALTER TABLE customers ALTER COLUMN name TYPE varchar(100);
-- or, with no limit at all:
ALTER TABLE customers ALTER COLUMN name TYPE text;

Increasing a varchar limit, or changing varchar to text, doesn’t rewrite the table. It still takes a brief ACCESS EXCLUSIVE lock, so on a busy table it waits for running queries; see changing a column’s type. Lowering the limit does rewrite the table, and fails with this same error if any existing value is too long.

In PostgreSQL, text and varchar without a length perform the same; a limit only adds a check. Keep one where the rule is real (a two-letter country code), and drop it where it was a guess.

Shorten the value deliberately

If the limit is right and the data should be cut, cut it on purpose:

INSERT INTO customers (id, name) VALUES (7, left('Augusta Ada King, Countess of Lovelace', 20));

An explicit cast does the same: '…'::varchar(20) truncates silently instead of failing, as the SQL standard requires. That’s easy to do by accident, so prefer left(), which makes the intent clear.

Skip bad rows in a COPY

On PostgreSQL 17 and later, COPY … WITH (ON_ERROR ignore) skips rows with values that don’t fit their column, including over-long strings, and reports how many it skipped. You lose those rows, so use it for data you can afford to drop or load again later.

Reproduce it

On PostgreSQL 18.6:

CREATE TABLE customers (id int PRIMARY KEY, country char(2), postcode varchar(8), name varchar(20));
INSERT INTO customers VALUES (1, 'GB', 'SW1A 1AA', 'Ada Lovelace');
INSERT INTO customers VALUES (2, 'GBR', 'SW1A 1AA', 'Ada Lovelace');
INSERT INTO customers VALUES (3, 'GB', 'SW1A 1AA', 'Augusta Ada King, Countess of Lovelace');

ERROR:  value too long for type character(2)
ERROR:  value too long for type character varying(20)

No column name and no position. Loading the same kind of data with COPY names the column and the value:

ERROR:  value too long for type character varying(20)
CONTEXT:  COPY customers, line 2, column name: "Rear Admiral Grace Brewster Hopper"

Some edge cases from the same session:

• UPDATE customers SET postcode = 'SW1A 1AA ' WHERE id = 1 succeeds: when the extra characters are all spaces, PostgreSQL trims them instead of failing.
• 'Zoë Ångström-Café' (17 characters, 21 bytes) fits in varchar(20).
• SELECT 'Augusta Ada King, Countess of Lovelace'::varchar(20) returns Augusta Ada King, Co with no error.

Widening name to varchar(100), then to text, kept the table’s relfilenode (no rewrite); changing it back to varchar(50) gave it a new one (a rewrite). ALTER COLUMN name TYPE varchar(5) with longer values in the table failed with value too long for type character varying(5). PostgreSQL 14, 15, 16 and 17 behave the same.

In Inlet

The structure editor changes a column’s type, shows the DDL, and warns when a change rewrites or scans the table. When a statement fails, Inlet shows the error at the position the server reports; with your own Anthropic API key, Ask Claude (⌘L) can fix it, sending the schema, the SQL and the error, never rows.