Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

An insert fails with value too long for type character varying(500)

ErrorERROR: value too long for type character varying(500)

Short answer

PostgreSQL refuses a string longer than the n in varchar(n); only trailing spaces are silently cut. Read the column's real limit from information_schema, then either shorten the data or widen the column with ALTER TABLE. In Django, a CharField max_length becomes that limit, so change the model and migrate.

  • Applies to PostgreSQL 14 to 17, Django 5.2
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the declared length of the column from the catalog, then compare it with the longest value you try to store.

    SELECT column_name, data_type, character_maximum_length
    FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'my_table';
  2. 2Match what you see, then jump to its fix

    1. 1character_maximum_length is 500 and the input has more charactersThe data really is longer than the column allowsFix
    2. 2The error comes from a Django save, and the model field is a CharField with max_length=500A Django CharField max_length is smaller than the dataFix
    3. 3The model or the SQL schema says a larger limit than information_schema showsThe database column is older than the code, so the limits differFix

What does the error look like?

  • ERROR: value too long for type character varying(500)
  • ERROR: value too long for type character varying(5)

Cause 1 of 3

The data really is longer than the column allows

1Check

PostgreSQL counts characters, and raises the error for any longer string unless the excess characters are all spaces. Measure the failing value.

SELECT max(char_length(body)) FROM staging_import;

2Fix

If long values are valid, widen the column. If they are not, trim or reject them in the application before the insert. A text column, or varchar without a length, takes any length.

ALTER TABLE my_table ALTER COLUMN body TYPE text;
-- or keep a limit, but a larger one
ALTER TABLE my_table ALTER COLUMN body TYPE varchar(2000);

3Verify

Insert the long value again, then read the column definition.

INSERT INTO my_table (body) VALUES (repeat('x', 1200));
SELECT character_maximum_length FROM information_schema.columns WHERE table_name = 'my_table' AND column_name = 'body';

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

A Django CharField max_length is smaller than the data

1Check

A CharField's max_length is enforced at the database level and in Django's validation, and it becomes the varchar length when the table is created. Find the field in your models.

grep -rn "max_length" --include=models.py .

2Fix

Raise max_length, or switch the field to TextField when there is no natural limit, then create and apply the migration.

# models.py
title = models.CharField(max_length=1000)   # or: models.TextField()

python manage.py makemigrations
python manage.py migrate

3Verify

The migration appears as applied, and the column length changed.

python manage.py showmigrations myapp

Confirmed by docs.djangoproject.com

Did this fix it?

Cause 3 of 3

The database column is older than the code, so the limits differ

1Check

A migration was never applied on this database, or the table was created by hand with a smaller length. Compare the catalog value with the code.

SELECT character_maximum_length FROM information_schema.columns
WHERE table_name = 'my_table' AND column_name = 'title';

2Fix

Alter the column to the intended type. Widening a varchar takes an ACCESS EXCLUSIVE lock while it runs, and large tables can take time, so do it in a quiet period and run ANALYZE afterwards.

ALTER TABLE my_table ALTER COLUMN title TYPE varchar(1000);
ANALYZE my_table;

3Verify

The catalog now shows the new length.

SELECT character_maximum_length FROM information_schema.columns WHERE table_name = 'my_table' AND column_name = 'title';

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Pick text, or varchar with no length, when there is no real business limit, as the PostgreSQL docs advise.
  • Validate length in the application, so users see a clear message instead of a database error.
  • Do not hide the problem with an explicit cast to varchar(n): that truncates silently and loses data.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.