PostgreSQL SERIAL - 创建一个自增列

在 PostgreSQL 中， SERIAL 是一种特殊的数据类型，以帮助您在表中创建一个自增列。

SERIAL 类型的列和标识列很类似，其内部都使用了序列。 SERIAL 引入的更早，但它不是 SQL 标准的一部分，而标识列则符合 SQL 标准。因此，推荐使用标识列。

创建 PostgreSQL SERIAL 列

要定义一个 SERIAL 列，请使用以下语法：

column_name SERIAL

这里， SERIAL 作为一个数据类型跟在列名的后面。

注意， SERIAL 列将自动添加 NOT NULL 约束，但不会自动成为主键列，但是您可以在列定义中使用 PRIMARY KEY 使该列成为主键列。

要在创建表时定义一个 SERIAL 列，请使用以下 CREATE TABLE 语句：

CREATE TABLE table_name (
  column_name SERIAL,
  ...
);

要在已有表中添加一个 SERIAL 列，请使用以下 ALTER TABLE 语句：

ALTER TABLE table_name
  ADD COLUMN column_name SERIAL;

PostgreSQL SERIAL 种类

您可以使用三种类型的 SERIAL 类型以应对不同的数据范围： SMALLSERIAL, SERIAL, BIGSERIAL。

PostgreSQL SERIAL 实例

以下语句创建以 id 列为 SERIAL 列的 test_serial 表：

CREATE TABLE test_serial(
   id SERIAL PRIMARY KEY,
   notes VARCHAR NOT NULL
);

要查看表的定义，请使用以下 \d 命令：

\d test_serial

                                 Table "public.test_serial"
 Column |       Type        | Collation | Nullable |                 Default
--------+-------------------+-----------+----------+-----------------------------------------
 id     | integer           |           | not null | nextval('test_serial_id_seq'::regclass)
 notes  | character varying |           | not null |
Indexes:
    "test_serial_pkey" PRIMARY KEY, btree (id)

这里：

• id 列的数据类型为 INTEGER。
• id 列是一个 NOT NULL 列。
• id 列具有默认值 nextval('test_serial_id_seq')。 test_serial_id_seq 是内部序列的名称，这是由 PostgreSQL 自动生成的。

您可以通过 pg_get_serial_sequence() 函数获取此列对应的内部序列名：

SELECT pg_get_serial_sequence('test_serial', 'id');

  pg_get_serial_sequence
---------------------------
 public.test_serial_id_seq
(1 row)

以下语句向 test_serial 表中插入几个新行，其中 id 是 SERIAL 列，它的值会自动生成：

INSERT INTO test_serial (notes)
VALUES ('A'), ('B'), ('C');

要查看 test_serial 表中的行，请使用以下语句：

SELECT * FROM test_serial;

 id | notes
----+-------
  1 | A
  2 | B
  3 | C
(3 rows)

获取 PostgreSQL SERIAL 列的当前的值

要获取 PostgreSQL SERIAL 列的当前的值，您可以使用 currval() 函数。

currval() 函数序列的名称作为参数，因此您需要先获取 SERIAL 列对应的内部序列的名称。 通常情况下， PostgreSQL 会按照如下格式为 SERIAL 列创建内部序列：

{table_name}_{column_name}_seq

或者您可以通过上面实例中使用的 \d 命令或者 pg_get_serial_sequence() 函数来获取此列对应的内部序列名。

然后，使用如下 currval() 函数获取 SERIAL 列的当前的值：

SELECT currval('test_serial_id_seq');

 currval
---------
       3
(1 row)

或者，您直接使用以下一个语句来获取 SERIAL 列的当前的值：

SELECT currval(pg_get_serial_sequence('test_serial', 'id'));

 currval
---------
       3
(1 row)

结论

PostgreSQL SERIAL 数据类型可以帮您创建一个具有自增序列的列。