奇怪的PostgreSQL“对于类型字符变化（500）来说值太长”。 [英] Strange PostgreSQL "value too long for type character varying(500)"

问题描述

我有一个Postgres模式，如下所示：

I have a Postgres schema which looks like:

问题是，每当我在描述列中保存长度超过500个字符的文本时，都会出现错误：

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)

在Postgres的文档中说类型文本可以有无限个字符。

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

我正在使用postgresql-9.1。

I'm using postgresql-9.1.

此表是使用Django 1.4生成的，模型中的字段类型为TextField，如果可以进一步说明问题的话。

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.

任何

推荐答案

通过将列指定为 VARCHAR（500），您已设置了明确的500个字符的限制。您可能没有明确地自己完成此操作，但是Django在某个地方为您完成了此操作。告诉您未显示模型，完整的错误文本或产生错误的查询在哪里困难。

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.

如果您不想要显示错误，使用不合格的 VARCHAR ，或使用 TEXT 类型。

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

varchar 和 text 的长度仅受系统对列大小的限制（约1GB）和您的记忆。但是，在 varchar 中添加长度限定符会手动设置一个较小的限制。以下所有参数在很大程度上都相同：

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) 

唯一的区别是在违反约束时如何报告数据库元数据以及引发哪个SQLSTATE。

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

所以我认为您 are 使用 VARCHAR（500）列，您正在查看错误的表或错误的数据库实例。

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

这篇关于奇怪的PostgreSQL“对于类型字符变化（500）来说值太长”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！