PostgreSQL IDENTITY COLUMN 标识列

PostgreSQL 标识列用于定义一个可以自动生成唯一值的列。

在 PostgreSQL 中，标识列是一个特殊的生成列，该列的值能自动生成并且是唯一的。标识列是定义在列上的约束。

标识列和 SERIAL 类似，他们的内部都使用 SEQUENCE 实现。

不像主键，一个表中允许存在多个标识列，并且标识列中允许存在重复的值。

标识列的语法

要创建标识列，请使用以下语法：

col_name data_type
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

解释说明：

  • 标识列的数据类型可以是：SMALLINT, INT，或 BIGINT。

  • GENERATED ALWAYS AS IDENTITY 列的值不能被写入。 向此列中插入（或更新）值将会导致一个错误。有一个特殊情况，就在 INSERT 语句中使用 OVERRIDING SYSTEM VALUE 指令可以向此列插入值。

  • GENERATED BY DEFAULT AS IDENTITY 列的值能被写入，包括插入和更新。

  • sequence_options 指明了标识列内部使用的序列的选项。它是可选的，如果不指定，PostgreSQL 使用默认值产生一个序列。

你可以在通过 CREATE TABLE 语句创建表时定义标识列，或者通过 ALTER TABLE 语句添加一个标识列。

在创建表时定义标识列

要在创建表时添加标识列，请使用如下语法：

CREATE TABLE table_name (
  column_definition,
  ...
  col_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ],
  ...
);

添加标识列

要在创建表后添加一个标识列，请使用 ALTER TABLE 语句：

ALTER TABLE table_name
  ADD COLUMN col_name data_type
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ];

将一个列设置表标识列

要将一个已有的列设置为标识列，请使用 ALTER TABLE 语句：

ALTER TABLE table_name
  ALTER COLUMN col_name
    ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ];

或者

ALTER TABLE table_name
  ALTER COLUMN col_name
    SET GENERATED { ALWAYS | BY DEFAULT };

取消一个标识列

要取消一个标识列，请使用 ALTER TABLE 语句：

ALTER TABLE table_name
  ALTER COLUMN col_name
    DROP IDENTITY [ IF EXISTS ];

PostgreSQL 标识列实例

下面有一些展示 PostgreSQL 标识列用法的实例。

创建标识列实例

使用下面的语句创建一个表，其中 id 列是标识列：

CREATE TABLE test_identity (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  txt VARCHAR(45) NOT NULL
);

您可以通过 \d 命令查看 test_identity 中标识列的情况：

\d test_identity

                             Table "public.test_identity"
 Column |         Type          | Collation | Nullable |           Default
--------+-----------------------+-----------+----------+------------------------------
 id     | integer               |           | not null | generated always as identity
 txt    | character varying(45) |           | not null |

使用以下语句插入 2 行测试数据：

INSERT INTO test_identity (txt)
VALUES ('Apple'), ('Peach');

使用以下语句查找 test_identity 表中的所有行：

SELECT * FROM test_identity;

 id |  txt
----+-------
  1 | Apple
  2 | Peach
(2 rows)

从输出可以看到， id 列中的自动生成的值是一个自增的序列。

向标识列中插入值实例

下面的语句尝试使用 INSERT 向标识列中插入一个值：

INSERT INTO test_identity (id, txt)
VALUES (1, 'Banana')
RETURNING *;

ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

这里，由于 id 列带有 GENERATED ALWAYS AS IDENTITY 约束，因此不能直接插入值。按照 PostgreSQL 的提示，可以使用 OVERRIDING SYSTEM VALUE 强制插入，如下：

INSERT INTO test_identity (id, txt)
OVERRIDING SYSTEM VALUE
VALUES (1, 'Banana')
RETURNING *;

 id |  txt
----+--------
  1 | Banana
(1 row)

同时，我们也可以看出，标识列是允许重复值的。

多个标识列实例

一个表中可以存在多个标识列，下面的语句添加一个标识列 id_2，并且起始值为 10，每次自增 10：

ALTER TABLE test_identity
  ADD COLUMN id_2 INTEGER GENERATED ALWAYS AS IDENTITY
    (START WITH 10 INCREMENT BY 10);

让我们看一下表中的数据：

SELECT * FROM test_identity;

 id |  txt   | id_2
----+--------+------
  1 | Apple  |   10
  2 | Peach  |   20
  1 | Banana |   30
(3 rows)

您可以发现，添加一个标识列后，已有的行中的标识列中的值已经自动生成。

使用以下语句插入 2 行测试数据：

INSERT INTO test_identity (txt)
VALUES ('Pear'), ('Cherry')
RETURNING *;

 id |  txt   | id_2
----+--------+-----
  3 | Pear   |  40
  4 | Cherry |  50
(2 rows)

结论

在本文中，我们学习了 PostgreSQL 标识列的用法。