我要评论摘要： 本文介绍了在 postgresql 中创建自增列的三种方法：直接使用序列（sequence）、使用 serial 数据类型，以及使用 identity column 语法。文章通过实际示例详细讲解了每种方法的语法、行为和使用场景。
使用生成键（generated key）是数据库管理员（dba）经常采用的做法，主要出于性能考虑。在理想情况下，我们本可以依赖表的自然主键就万事大吉。然而，使用人工主键已被证明对性能有益。
在这篇博客中，我不会解释什么是主键或什么是自然键/人工键。如果你对数据库设计感兴趣，可以找到很多优秀的书籍。
在这篇博客中，我将介绍在 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） | serial | identity 列 |
|---|---|---|---|
| 自动使用 nextval 作为默认值 | 否 | 是 | 是 |
| 非空约束 | 否 | 是 | 是 |
| 阻止手动插入 | 否 | 否 | 使用 always 时支持 |
到此这篇关于在postgresql中实现自增的三种方式的文章就介绍到这了,更多相关postgresql实现自增内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论