PostgreSQL：串行与身份 [英] PostgreSQL: serial vs identity

问题描述

要在表上具有整数自动编号主键，可以使用 SERIAL

To have an integer auto-numbering primary key on a table, you can use SERIAL

但是我注意到表 information_schema。列具有许多 identity _ 字段，实际上，您可以创建带有 GENERATED 说明符 ...

But I noticed the table information_schema.columns has a number of identity_ fields, and indeed, you could create a column with a GENERATED specifier...

有什么区别？他们引入了不同的PostgreSQL版本吗？

What's the difference? Were they introduced with different PostgreSQL versions? Is one preferred over the other?

推荐答案

serial 是旧的吗？ 实现自动生成的唯一值已是Postgres的一部分了。但是，这不是SQL标准的一部分。

serial is the "old" implementation of auto-generated unique values that has been part of Postgres for ages. However that is not part of the SQL standard.

为了更加符合SQL标准，Postgres 10引入了使用作为身份生成的语法。

To be more compliant with the SQL standard, Postgres 10 introduced the syntax using generated as identity.

基础实现仍基于序列，该定义现在符合SQL标准。新语法允许的一件事是防止意外覆盖该值。

The underlying implementation is still based on a sequence, the definition now complies with the SQL standard. One thing that this new syntax allows is to prevent an accidental override of the value.

请考虑以下表：

create table t1 (id serial primary key);
create table t2 (id integer primary key generated always as identity);

现在运行时：

insert into t1 (id) values (1);

基础序列和表中的值不再同步。如果您再运行

The underlying sequence and the values in the table are not in sync any more. If you run another

insert into t1 default_values;

您会收到一条错误消息，因为该序列在第一次插入时并未推进，现在尝试插入再次输入值 1 。

You will get an error because the sequence was not advanced by the first insert, and now tries to insert the value 1 again.

但是在第二张表中，

insert into t2 (id) values (1);

结果：

ERROR: cannot insert into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.

所以您可能会意外地忘记序列用法。您仍然可以使用覆盖系统值选项来强制执行此操作：

So you can accidentally "forget" the sequence usage. You can still force this, using the override system value option:

insert into t2 (id) overriding system value values (1);

仍然使您的序列与表中的值不同步，但至少让您意识到了这一点。

which still leaves you with a sequence that is out-of-sync with the values in the table, but at least you were made aware of that.

建议使用新的身份语法而不是串行的

It is recommended to use the new identity syntax rather than serial

这篇关于PostgreSQL：串行与身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！