Strange PostgreSQL "value too long for type character varying(500)"

Question

I have a Postgres schema which looks like:

The problem is that whenever I save text longer than 500 characters in the description column I get the error:

value too long for type character varying(500) 

In the documentation for Postgres it says type text can have unlimited characters.

I'm using postgresql-9.1.

This table has been generated using Django 1.4 and the field type in the model is TextField, if that helps explain the problem further.

Any ideas as why this is happening and what I can do to fix it?

Answer 1

By specifying the column as VARCHAR(500) you've set an explicit 500 character limit. You might not have done this yourself explicitly, but Django has done it for you somewhere. Telling you where is hard when you haven't shown your model, the full error text, or the query that produced the error.

If you don't want one, use an unqualified VARCHAR, or use the TEXT type.

varchar and text are limited in length only by the system limits on column size - about 1GB - and by your memory. However, adding a length-qualifier to varchar sets a smaller limit manually. All of the following are largely equivalent:

column_name VARCHAR(500)  column_name VARCHAR CHECK (length(column_name) <= 500)   column_name TEXT CHECK (length(column_name) <= 500)  

The only differences are in how database metadata is reported and which SQLSTATE is raised when the constraint is violated.

The length constraint is not generally obeyed in prepared statement parameters, function calls, etc, as shown:

regress=> \x Expanded display is on. regress=> PREPARE t2(varchar(500)) AS SELECT $1; PREPARE regress=> EXECUTE t2( repeat('x',601) ); -[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ?column? | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 

and in explicit casts it result in truncation:

regress=> SELECT repeat('x',501)::varchar(1); -[ RECORD 1 ] repeat | x 

so I think you are using a VARCHAR(500) column, and you're looking at the wrong table or wrong instance of the database.