Postgres 实现自增的三种方式

hudson2022
0 阅读4分钟

Postgres 实现自增的三种方式

摘要： 本文介绍了在 PostgreSQL 中创建自增列的三种方法：直接使用序列（sequence）、使用 serial 数据类型，以及使用 identity column 语法。文章通过实际示例详细讲解了每种方法的语法、行为和使用场景。

原文链接

使用生成键（generated key）是数据库管理员（DBA）经常采用的做法，主要出于性能考虑。在理想情况下，我们本可以依赖表的自然主键就万事大吉。然而，使用人工主键已被证明对性能有益。

在这篇博客中，我不会解释什么是主键或什么是自然键/人工键。如果你对数据库设计感兴趣，可以找到很多优秀的书籍。如果你认为数据库设计是过时的东西不该再做了，你可能会对 Stonebraker 博士的这篇博客感兴趣：www.enterprisedb.com/blog/schema…

在这篇博客中，我将介绍在 Postgres 中实现自增的三种方式。

序列（Sequences）

实现自增数字的第一个明显方法是使用序列（sequence）。（实际上，我们稍后会看到其他方法也依赖于序列）。

以下是如何使用序列的简单示例：

laetitia=# create table test(id integeri primary key, value text);
CREATE TABLE
laetitia=# create sequence my_seq;
CREATE SEQUENCE
laetitia=# insert into test (select nextval('my_seq'), 'blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value
----+--------
  1 | blabla
(1 row)

但我们可以做得更好。如果我们希望 id 列自动填充序列的下一个值呢？

laetitia=# create sequence my_seq;
CREATE SEQUENCE
laetitia=# create table test (id integer default nextval('my_seq') primary key, value text);
CREATE TABLE
laetitia=# insert into test(value) values ('blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value
----+--------
  1 | blabla
(1 row)

以上就是使用 Postgres 实现自增 id 的第一种方法。

Serial 数据类型

自 Postgres 8.2（2006年发布）以来，Postgres 添加了 serial 数据类型。正如 Postgres 文档所述，它会做与我们之前相同的事情，但你只需用一个词就能完成。（参见 www.postgresql.org/docs/curren…

laetitia=# create table test (id serial primary key, value text);
CREATE TABLE
laetitia=# insert into test (value) values ('blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value
----+--------
  1 | blabla
(1 row)

如果查看表结构，你会发现已经创建了一个序列，并用作 id 列的默认值：

laetitia=# \d test
                            Table "public.test"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('test_id_seq'::regclass)
 value  | text    |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
laetitia=# \ds
             List of relations
 Schema |    Name     |   Type   |  Owner
--------+-------------+----------+----------
 public | test_id_seq | sequence | laetitia
(1 row)

Identity 列

序列是符合 SQL 标准的。然而，serial 数据类型并不符合标准。但 SQL 有一种方法可以为列创建自增，而不需要显式创建序列。这就是 generated as identity。

根据需求不同，该语法有两种形式。

第一种用法是当你想将序列值作为默认值，但允许手动输入其他值时。在这种情况下，语法是 generated by default as identity。但这不会阻止某人绕过序列直接插入值：

laetitia=# create table test (id integer generated by default as identity primary key, value text);
CREATE TABLE
laetitia=# \d test
                            Table "public.test"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
 value  | text    |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
laetitia=# insert into test (value) values ('blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value
----+--------
  1 | blabla
(1 row)
laetitia=# insert into test (id, value) values (2,'blabla');
INSERT 0 1
laetitia=# select * from test;
 id | value
----+--------
  1 | blabla
  2 | blabla
(2 rows)
laetitia=# insert into test (value) values ('blabla');
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(2) already exists.

如你所见，你可以手动添加值，然后由于序列滞后于 id 列中的最后一个数字，insert 操作会因重复键冲突而报错。

为了防止这种情况发生，我们可以创建一个约束来阻止任何人手动向该列插入数据。这就是另一种 generated as identity 语法。

laetitia=# create table test (id integer generated always as identity primary key, value text);
CREATE TABLE
laetitia=# \d test
                          Table "public.test"
 Column |  Type   | Collation | Nullable |           Default
--------+---------+-----------+----------+------------------------------
 id     | integer |           | not null | generated always as identity
 value  | text    |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
laetitia=# insert into test (value) values ('blabla');
INSERT 0 1
laetitia=# insert into test (id, value) values (2,'blabla');
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

我们仍然可以检查底层是否使用了序列：

laetitia=# \ds
Did not find any relations.
laetitia=# create table test (id integer generated always as identity primary key, value text);
CREATE TABLE
laetitia=# \ds
             List of relations
 Schema |    Name     |   Type   |  Owner
--------+-------------+----------+----------
 public | test_id_seq | sequence | laetitia
(1 row)

总结一下，有几种方法可以为列创建自增值。我建议几乎在所有情况下都使用 generated always as identity，因为此语法会添加约束以防止因手动插入而导致的序列不同步。当然，如果有人想要篡改 identity 列背后的序列，只要有适当的权限，他们总能找到方法做到这一点。

特性序列（Sequence）SerialIdentity 列
自动使用 nextval 作为默认值否是是
非空约束否是是
阻止手动插入否否使用 always 时支持
avatar
文章
阅读
粉丝