使用数据类型“文本"的任何不利之处都在于:用于存储字符串? [英] Any downsides of using data type "text" for storing strings?

问题描述

根据 Postgres文档，它们支持3种用于字符数据的数据类型:

According to the Postgres documentation, they support 3 data-types for character data:

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

在我的应用程序中，我遇到了几种不愉快的情况，其中插入/更新查询失败，因为要插入的所需文本超出了varchar(n)或char(n)的限制.

In my application, I came across few unpleasant scenarios where insert/update queries failed as the desired text to be inserted exceeded the varchar(n) or char(n) limit.

在这种情况下，请将此类列的数据类型更改为text就足够了.

For such cases, changing the data type of such columns to text sufficed.

我的问题是:
如果我们将每个字符存储列的数据类型通用化并更改为text，那么在性能/内存方面是否有不利之处?
如果数据类型为text的列每次存储10个或更少的字符，我应该选择text还是varchar(10)?
如果我选择text，会有什么弊端?

My questions are:
If we generalize and change the data type of every character storing column to text, is there any downside in terms of performance/memory?
If a column with data type text stores 10 or less characters every time, should I go for text or varchar(10)?
If I go for text what's the downside?

推荐答案

通常，使用

Generally, there is no downside to using text in terms of performance/memory. On the contrary: text is the optimum. Other types have more or less relevant downsides. @Quassnoi and @Guffa have already shed some light on this.

尤其是，从不不要使用 char 或 char(n) ( character / )，除非您知道自己在做什么.此空白填充类型仅用于与旧代码和标准兼容.如今，它变得毫无意义，浪费了内存并可能造成麻烦:

In particular, never use char or char(n) (alias for character / character(n)), unless you know what you are doing. This blank-padded type is only there for compatibility with old code and standards. It makes very little sense nowadays, wastes memory and is likely to cause trouble:

• 比较varchar和char
• Postgres SQL中的字符串字段长度

• Compare varchar with char
• String field length in Postgres SQL

要在列上强制使用最大长度，请仍然使用 text (或 CHECK约束以后进行更改(无需重写表)更加方便，甚至在视图，函数，FK约束等取决于列类型时更是如此.

To enforce a maximum length on a column, still use text (or varchar without length specifier, which is basically the same) and not varchar(n) (alias for character varying / character varying(n)). A CHECK constraint is much more convenient to change later (without table rewrite), even more so when views, functions, FK constraints etc. depend on the column type.

ALTER TABLE tbl ADD CONSTRAINT tbl_col_len CHECK (length(col) < 100);

CHECK约束不仅可以执行最大字符长度的操作，还可以做更多的事情-您可以在布尔表达式中添加的任何内容.了解更多:

A CHECK constraint can also do more than just enforce a maximum character length - anything you can put into a boolean expression. Read more:

• 更改视图中使用的PostgreSQL列

最后，还有 "char" (带双引号):单个ASCII字母的1字节数据类型，用作便宜的内部枚举类型.

Finally, there is also "char" (with double-quotes): a 1-byte data type for a single ASCII letter used as cheap internal enumeration type.

在Postgres中，我很少使用text以外的字符数据.

I rarely use anything but text for character data in Postgres.

这篇关于使用数据类型“文本"的任何不利之处都在于:用于存储字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！