Value Too Long For Character Varying On Narrowing
A column narrowing (ALTER ... TYPE varchar(n) or char(n)), an INSERT/UPDATE, or a load into a width-limited column fails with SQLSTATE 22001 and 'value...
In 10 seconds
A cleanup migration drops a legacy version suffix and tightens products.sku from a wide varchar(40) to varchar(8): ALTER TABLE products ALTER COLUMN sku TYPE varchar(8). It fails instantly with 'ERROR: value too long for type character varying(8)' (SQLSTATE 22001). Most SKUs are already 8 characters, but five legacy rows still carry a '-V1' suffix (length 11). ALTER COLUMN ... TYPE re-validates every existing value against the new width as an assignment, and because the excess is real content -- not trailing whitespace -- PostgreSQL refuses to truncate it silently and aborts the whole change. The migration looks blocked, but it just surfaced rows that do not fit the rule you are trying to enforce.
Why This Happens
character varying(n) enforces a maximum length on assignment. When you ALTER COLUMN ... TYPE varchar(8), PostgreSQL rewrites the table and coerces every existing value into the new type; the coercion is an assignment, so any value longer than 8 with non-space characters past the limit raises 22001 and the entire ALTER rolls back -- there is no partial rewrite.
The SQL standard mandates this 'string data, right truncation' error for assignment so data is never silently lost; the one exception is trailing spaces, which the standard allows to be trimmed to fit. The remedy is therefore about the data: confirm what the excess actually is, then trim or correct it so every value fits before re-running the narrowing.
Once the values fit, the column adopts the new width and enforces it on every future write. Once the values fit, the column adopts the new width and enforces it on every future write.
Diagnose
Free Tier -- Basic Approach
Before changing anything, size the problem with one count: how many rows are wider than the target width of 8. Five. The cleanup is small and bounded, not an unknown.
SELECT count(*) AS too_long_rows FROM products WHERE length(sku) > 8;
too_long_rows
---------------
5
(1 row)
SELECT count(*) AS too_long_rows FROM products WHERE length(sku) > 8; -- compare the stored length directly to the target width to see exactly how many values the narrowing will reject.
Advanced Approach -- Production-Safe
The free count says how many; this premium evidence shows WHAT the overflow is, so the cleanup is provably safe instead of a blind truncation. Part 1 splits each offender into its base code and suffix (every base is 8 chars); part 2 proves every overflow is only the dropped suffix (suffix_only = 5, base_still_too_long = 0, max_len = 11) -- so stripping '-V1' loses nothing that identifies the product.
SELECT id, sku, length(sku) AS raw_len,
split_part(sku,'-',1) AS base_code,
length(split_part(sku,'-',1)) AS base_len
FROM products WHERE length(sku) > 8 ORDER BY id;
id | sku | raw_len | base_code | base_len ------+-------------+---------+-----------+---------- 996 | SK000996-V1 | 11 | SK000996 | 8 997 | SK000997-V1 | 11 | SK000997 | 8 998 | SK000998-V1 | 11 | SK000998 | 8 999 | SK000999-V1 | 11 | SK000999 | 8 1000 | SK001000-V1 | 11 | SK001000 | 8 (5 rows)
SELECT max(length(sku)) AS max_len,
count(*) FILTER (WHERE length(split_part(sku,'-',1)) <= 8) AS suffix_only,
count(*) FILTER (WHERE length(split_part(sku,'-',1)) > 8) AS base_still_too_long
FROM products WHERE length(sku) > 8;
max_len | suffix_only | base_still_too_long
---------+-------------+---------------------
11 | 5 | 0
(1 row)
List offenders with SELECT id, sku, length(sku), split_part(sku,'-',1) AS base_code, length(split_part(sku,'-',1)) ... WHERE length(sku) > 8, then summarize with count(*) FILTER (WHERE length(split_part(sku,'-',1)) <= 8) vs FILTER (WHERE ... > 8).
If base_still_too_long were non-zero, the strip would not be lossless.
Fix
The premium split proved every overflow is just the '-V1' suffix and every base code already fits in 8 characters, so stripping the suffix is lossless for the identifying data. UPDATE the five rows (UPDATE 5), then the same narrowing that failed now succeeds (ALTER TABLE).
UPDATE products SET sku = split_part(sku,'-',1) WHERE length(sku) > 8; ALTER TABLE products ALTER COLUMN sku TYPE varchar(8);
UPDATE 5 ALTER TABLE
UPDATE products SET sku = split_part(sku,'-',1) WHERE length(sku) > 8; then ALTER TABLE products ALTER COLUMN sku TYPE varchar(8); -- the UPDATE only touches the over-length rows, and because each base code is <= 8 the subsequent rewrite has nothing left to reject.
Verify
Confirm the column adopted the new width, no value exceeds it, and the width is now enforced. information_schema reports character varying with maximum length 8, the over-length count is 0, and an insert of a value with real content past the limit is rejected with 'value too long for type character varying(8)' -- the run-time enforcement you wanted.
SELECT data_type, character_maximum_length FROM information_schema.columns WHERE table_name = 'products' AND column_name = 'sku';
data_type | character_maximum_length -------------------+-------------------------- character varying | 8 (1 row)
SELECT count(*) AS too_long_rows FROM products WHERE length(sku) > 8;
too_long_rows
---------------
0
(1 row)
INSERT INTO products(sku) VALUES ('SK000001-EXTRA');
ERROR: value too long for type character varying(8)
Read data_type and character_maximum_length from information_schema.columns, re-run count(*) WHERE length(sku) > 8 (expect 0), and attempt INSERT ... VALUES ('SK000001-EXTRA') to confirm it is rejected with SQLSTATE 22001.
⏪ Rollback
Nothing to undo from the failed ALTER -- it rolled back atomically and the column still has its original (wider) definition and unchanged data. Recovery is forward: count and inspect the over-length values, decide whether the excess is a safely-droppable suffix or genuine content that must be shortened or kept, fix the values with an UPDATE, then re-run the narrowing. The only data-changing step is that UPDATE; before running it, use the premium split so you can prove the change is lossless (here every base code already fits in 8 characters, so stripping the '-V1' suffix loses nothing meaningful).
📦 Version Matrix (PG 14--18)
The behavior is identical on PostgreSQL 14 through 18: narrowing with the '-V1' rows present raises 'value too long for type character varying', and after stripping the suffix the same ALTER returns ALTER TABLE. The 22001 assignment-truncation contract is stable across all supported majors.
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=value too long for type character varying | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=value too long for type character varying | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=value too long for type character varying | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=value too long for type character varying | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=value too long for type character varying | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Why didn't PostgreSQL just truncate the value to fit?
Because storing a value into character varying(n) is an assignment, and the SQL standard requires assignment to raise 22001 (string_data_right_truncation) rather than silently lose characters. The only exception is trailing spaces, which may be trimmed. An explicit value::varchar(n) cast is treated differently and does truncate silently -- which is exactly why you should not use it as a 'fix'.
The error doesn't name the row -- how do I find the offenders?
Test length against the target width: SELECT id, sku, length(sku) FROM products WHERE length(sku) > 8. To understand the overflow, split the value (here split_part(sku,'-',1)) and compare the base length to the limit, so you can see whether the excess is a droppable suffix or genuine content.
Is it safe to strip the suffix?
Only after proving it. The premium check shows every base code already fits in 8 characters (suffix_only = 5, base_still_too_long = 0), so stripping '-V1' is lossless for the identifying part. If any base code had exceeded 8, that would be a real truncation decision requiring a business call, not an automatic strip.
Should I change the type at all, or just enforce a length?
If you only need to prevent long values, a CHECK (length(sku) <= 8) enforces the rule without rewriting the table or locking it as heavily as a type change, and it documents intent. Change the type when the storage/format itself must change; add a CHECK when you just need a guardrail.
How do I keep over-length values from coming back?
Fix the upstream source that produced the '-V1' values, keep the narrowed type (or a CHECK) so new writes are rejected, and run a standing guard -- SELECT count(*) WHERE length(sku) > 8 -- that must read 0. Remember it can miss trailing-space-only overflows, which are truncated silently.