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?

Solution 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.

Solution 2

Character varying is different than text. Try running

ALTER TABLE product_product ALTER COLUMN code TYPE text;

That will change the column type to text, which is limited to some very large amount of data (you would probably never actually hit it.)

Solution 3

We had this same issue. We solved it adding 'length' to entity attribute definition:

@Column(columnDefinition="text", length=10485760)
private String configFileXml = ""; 

Comments

[juergen d]

possible duplicate of Django fixture fails, stating "DatabaseError: value too long for type character varying(50)"

[Robert H]

Does the issue occur with psql inserts, or only from django?

[Craig Ringer]

Since you've generated the table from a Django model, how about showing your Django model code?

[Parham]

I just checked the psql insert through command line the error doesn't occur there. It might be an encoding problem.

[Scott S]

Is it possible that this column was character varying(500), but has been changed to text, but the change hasn't been committed, and so the django client can't see the change? That seems unlikely, but the error from django definitely indicates that for it, the column is char var, while we clearly see from this output that it is text.

[Craig Ringer]

No, it is not an encoding problem. Please show the actual INSERT statement generated by Django, and the full, exact text of the error message. You can get both from the PostgreSQL log files.

[Parham]

I just checked the log file the error was being occurred when a related field was being saved, which wasn't abiding by the 500 char constraint

[Craig Ringer]

Glad to hear you have a solution. Now you know one more thing to check, and one more thing to include in future questions :-)

[Parham]

Thanks for your answer, introduced me to a very handy piece of knowledge for tracing bugs.

[Parham]

I edited the question, the problem is with the description field which is already type text

[Parham]

I'm using type text look at the description column

[Craig Ringer]

@Parham I see that. The explanation remains relevant; varchar(500) is being used somewhere. Please show the actual query in your question so we can see the query that's failing. If you can't get it from Django, get it from the PostgreSQL log files and edit your question to add the query to the question. You might need to set log_statement = 'all' in postgresql.conf and reload PostgreSQL if the full query text isn't logged alongside the error.

[Parham]

I just checked the log files as you suggested it turns out that the error was occurring when another related field was being saved

[Peter Kam]

you must have deleted all data first then. right? show here how to delete them all. Well done