postgresql 如何向文本字段添加长度约束

How to add a length constraint to a text field

提问by Tobias

It seems it is preferable to use the TEXTdatatype when using PostgreSQL (or some other databases which support it as well) rather than character varying(NN)because there is no performance penalty, and the maximum possible length can be adjusted by dropping and re-applying constraints without effecting any views etc. which use the field.

似乎TEXT在使用 PostgreSQL（或其他一些支持它的数据库）时最好使用数据类型，而不是character varying(NN)因为没有性能损失，并且可以通过删除和重新应用约束来调整最大可能长度而不影响任何使用该字段的视图等。

But, how is this constraint applied (SQL code)?

但是，这个约束是如何应用的（SQL 代码）？

回答by gmaliar

When you create the table you can do something of this sort,

当你创建表格时，你可以做这样的事情，

CREATE TABLE names (
  name text CONSTRAINT namechk CHECK (char_length(name) <= 255)
)

(namechkis just a name for the constraint)

（namechk只是约束的名称）

Same goes for ALTER TABLEfor example:

这同样适用于ALTER TABLE例如：

ALTER TABLE names
  ADD CONSTRAINT namechk CHECK (char_length(name) <= 255);

回答by IMSoP

There are really three things here:

这里真的有三件事：

1. Is it better to use text+ a check constraint, or varchar(N)?
2. How would you write an appropriate check constraint?
3. Should you name your constraints, or let an automatic name be assigned?

1. 使用text+ 检查约束更好，还是使用varchar(N)?
2. 您将如何编写适当的检查约束？
3. 您应该命名约束，还是分配一个自动名称？

Answers:

答案：

1. A varchar(N)will be more obvious when inspecting the schema, and what developers coming from other DBs will expect to see. However, as you say, it is harder to change later. Bear in mind that applying a new/modified check constraint is not free - all existing rows must be checked against the constraint, so on a large table, a lot of reading is necessary.
2. The syntax for a check constraint is CONSTRAINT name CHECK (condition)(or just CHECK (condition)and Postgres itself will come up with a name) in a CREATE TABLEstatement, and ALTER TABLE table_name ADD CONSTRAINT name CHECK (condition);. conditionwould be an expression using an appropriate string function, e.g. char_length(foo) <= 255.
3. Adding a name for a constraint is very useful if you want to manage the constraint later. In particular, since you're using this for flexibility, you may well want to write code to drop and recreate the constraint with a new length. If you only ever use graphical tools, this isn't a problem, but managing multiple servers (e.g. development, testing, and production copies) becomes much easier if you can script your changes. With a named constraint, this would like ALTER TABLE foo DROP CONSTRAINT ck_bar_length; ALTER TABLE foo ADD CONSTRAINT ck_bar_length CHECK ( char_length(bar) <= 100 );I can't actually think of a disadvantageof naming your constraint.

1. varchar(N)在检查模式时，A会更明显，以及来自其他 DB 的开发人员希望看到的内容。然而，正如你所说，以后更难改变。请记住，应用新的/修改的检查约束不是免费的 - 必须根据约束检查所有现有行，因此在大表上，需要大量读取。
2. 检查约束的语法是CONSTRAINT name CHECK (condition)（或者只是CHECK (condition)并且 Postgres 本身会提供一个名称）在一个CREATE TABLE语句中，并且ALTER TABLE table_name ADD CONSTRAINT name CHECK (condition);. condition将是使用适当字符串函数的表达式，例如char_length(foo) <= 255.
3. 如果您想稍后管理约束，则为约束添加名称非常有用。特别是，由于您使用它是为了灵活性，您可能希望编写代码来删除和重新创建具有新长度的约束。如果您只使用图形工具，这不是问题，但如果您可以编写更改脚本，则管理多个服务器（例如开发、测试和生产副本）会变得更加容易。使用命名约束，这就像ALTER TABLE foo DROP CONSTRAINT ck_bar_length; ALTER TABLE foo ADD CONSTRAINT ck_bar_length CHECK ( char_length(bar) <= 100 );我实际上无法想到命名约束的缺点。