PostgreSQL 教程: 使用 serial 创建自增列

八月 23, 2023

摘要:在本教程中,您将了解 PostgreSQL 的SERIAL伪类型,以及如何使用SERIAL伪类型定义表中的自增列。

PostgreSQL Serial

PostgreSQL SERIAL 伪类型简介

在 PostgreSQL 中,序列是一种特殊的数据库对象,它生成整数序列。序列通常用作表中的主键列。

创建新表时,可以通过SERIAL伪类型创建序列,如下:

CREATE TABLE table_name(
    id SERIAL
);

通过将SERIAL伪类型分配给id列,PostgreSQL 执行以下操作:

  • 首先,创建一个序列对象,并将序列生成的下一个值设置为该列的默认值。
  • 其次,向id列添加NOT NULL约束,因为序列总是生成一个整数,该整数是非空值。
  • 第三步,将序列的所有者分配给id列;因此,当删除id列或表时,序列对象也会被删除。

实际上,如下的语句:

CREATE TABLE table_name(
    id SERIAL
);

等价于下面的语句:

CREATE SEQUENCE table_name_id_seq;

CREATE TABLE table_name (
    id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);

ALTER SEQUENCE table_name_id_seq
OWNED BY table_name.id;

PostgreSQL 提供了三种 serial 伪类型:SMALLSERIALSERIALBIGSERIAL,它们具有以下特点:

名称 存储大小 范围
SMALLSERIAL 2 字节 1 至 32,767
SERIAL 4 字节 1 至 2,147,483,647
BIGSERIAL 8 字节 1 至 9,223,372,036,854,775,807

PostgreSQL SERIAL 示例

需要注意的是,SERIAL不会隐式地在列上创建索引或使该列成为主键列。然而,这可以通过指定SERIAL列的PRIMARY KEY约束来轻松完成。

以下语句创建以id列为SERIAL列的fruits表:

CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

要在向表中插入行时为serial列指定默认值,请忽略列名或在INSERT语句中使用DEFAULT关键字。

请参见以下示例:

INSERT INTO fruits(name) 
VALUES('Orange');

或者

INSERT INTO fruits(id,name) 
VALUES(DEFAULT,'Apple');

PostgreSQL 向fruits表中插入了两行,id 列的值为 1 和 2。

SELECT * FROM fruits;
 id |  name
----+--------
  1 | Apple
  2 | Orange
(2 rows)

要获取表中SERIAL列的序列名称,请使用pg_get_serial_sequence()函数,如下:

pg_get_serial_sequence('table_name','column_name')

您可以将序列名称传递给currval()函数以获取序列生成的最新值。例如,以下语句返回fruits_id_seq对象生成的最近值:

SELECT currval(pg_get_serial_sequence('fruits', 'id'));
currval
---------
2
(1 row)

如果要在向表中插入新行时获取序列生成的值,请在INSERT语句中使用RETURNING id子句。

以下语句将新行插入fruits表中,并返回为 id 列生成的值。

INSERT INTO fruits(name) 
VALUES('Banana')
RETURNING id;
id
----
3
(1 row)

序列生成器操作不是事务安全的。这意味着如果两个并发的数据库连接尝试从序列中获取下一个值,则每个客户端将获得不同的值。如果一个客户端回滚事务,则该客户端的序列号将未被使用,从而在序列中产生间隙。

在本教程中,您学习了如何使用 PostgreSQL 伪类型SERIAL为表创建自增列。