database PostgreSQL 字符变长限制

PostgreSQL character varying length limit

提问by Anton

I am using character varying data type in PostgreSQL. I was not able to find this information in PostgreSQL manual. What is max limit of characters in character varying data type?

我在 PostgreSQL 中使用字符变化的数据类型。我无法在 PostgreSQL 手册中找到此信息。字符变化数据类型中字符的最大限制是多少？

采纳答案by Frank Bollack

Referring to the documentation, there is no explicit limit given for the varchar(n)type definition. But:

参考文档，varchar(n)类型定义没有明确限制。但：

...
In any case, the longest possible character string that can be stored is about 1 GB. (The maximum value that will be allowed for nin the data type declaration is less than that. It wouldn't be very useful to change this because with multibyte character encodings the number of characters and bytes can be quite different anyway. If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.)

...
无论如何，可以存储的最长字符串大约是 1 GB。（n数据类型声明中允许的最大值小于该值。更改它不会很有用，因为对于多字节字符编码，字符数和字节数无论如何都可能完全不同。如果您愿意存储没有特定上限的长字符串，使用没有长度说明符的文本或字符变化，而不是构成任意长度限制。）

Also note this:

还要注意这一点：

Tip:There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

提示：这三种类型之间没有性能差异，除了使用空白填充类型时增加了存储空间，以及在存储到长度受限列时需要额外的一些 CPU 周期来检查长度。虽然 character(n) 在其他一些数据库系统中具有性能优势，但在 PostgreSQL 中没有这样的优势；事实上 character(n) 通常是三个中最慢的，因为它有额外的存储成本。在大多数情况下，应改用文本或字符变化。

回答by Quassnoi

From documentation:

从文档：

In any case, the longest possible character string that can be stored is about 1 GB.

在任何情况下，可以存储的最长字符串大约是 1 GB。

回答by johan indra Permana

character type in postgresql

postgresql 中的字符类型

• character varying(n), varchar(n) = variable-length with limit
• character(n), char(n) = fixed-length, blank padded
• text = variable unlimited length

• 字符变化(n), varchar(n) = 有限制的可变长度
• character(n), char(n) = 固定长度，空白填充
• 文本 = 可变的无限长度

based on your problem I suggest you to use type text. the type does not require character length.

根据您的问题，我建议您使用 type text。该类型不需要字符长度。

In addition, PostgreSQL provides the texttype, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

另外，PostgreSQL 提供了text类型，可以存储任意长度的字符串。尽管类型文本不在 SQL 标准中，但其他几个 SQL 数据库管理系统也有。

source : https://www.postgresql.org/docs/9.6/static/datatype-character.html

来源：https: //www.postgresql.org/docs/9.6/static/datatype-character.html

回答by Brad Koch

The maximum string size is about 1 GB. Per the postgres docs:

最大字符串大小约为1 GB。根据 postgres文档：

Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB. (The maximum value that will be allowed for nin the data type declaration is less than that.)

很长的值也存储在后台表中，这样它们就不会干扰对较短列值的快速访问。在任何情况下，可以存储的最长字符串大约是 1 GB。（数据类型声明中允许的n的最大值小于该值。）

Note that the max nyou can specify for varchar is less than the max storage size. While this limit may vary, a quick check reveals that the limit on postgres 11.2 is 10 MB:

请注意，您可以为 varchar 指定的最大n小于最大存储大小。虽然此限制可能会有所不同，但快速检查会发现 postgres 11.2 的限制为10 MB：

psql (11.2)

=> create table varchar_test (name varchar(1073741824));
ERROR:  length for type varchar cannot exceed 10485760

Practically speaking, when you do not have a well rationalized length limit, it's suggestedthat you simply use varcharwithout specifying one. Per the official docs,

实际上，当您没有一个合理的长度限制时，建议您直接使用varchar而不指定一个。根据官方文档，

If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.

如果您希望存储没有特定上限的长字符串，请使用没有长度说明符的文本或字符变化，而不是构成任意长度限制。