PostgreSQL 教程: 标识列

九月 12, 2023

摘要：在本教程中，您将学习如何使用GENERATED AS IDENTITY约束为表创建标识列。

PostgreSQL 标识列简介

PostgreSQL 版本 10 引入了一个新的约束GENERATED AS IDENTITY，允许您自动为列分配唯一的编号。

GENERATED AS IDENTITY约束是旧的SERIAL列符合 SQL 标准的变体。

下面说明了GENERATED AS IDENTITY约束的语法：

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

在这个语法中：

  • 类型可以是SMALLINT、INT或BIGINT。
  • GENERATED ALWAYS指示 PostgreSQL 始终为标识列生成一个值。如果您尝试向GENERATED ALWAYS AS IDENTITY列中插入（或更新）值，PostgreSQL 将发出错误。
  • GENERATED BY DEFAULT也指示 PostgreSQL 为标识列生成一个值。但是，如果您为插入或更新提供一个值，PostgreSQL 将使用该值插入到标识列中，而不是使用系统生成的值。

PostgreSQL 允许一张表拥有多个标识列。与SERIAL一样，GENERATED AS IDENTITY约束也在内部使用SEQUENCE对象。

PostgreSQL 标识列示例

A) GENERATED ALWAYS 例子

首先，创建一个表，名为color，以color_id为标识列：

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

其次，向color表中插入新行：

INSERT INTO color(color_name)
VALUES ('Red');

由于color_id列具有GENERATED AS IDENTITY约束，PostgreSQL 会为其生成一个值，如下面的查询所示：

SELECT * FROM color;

PostgreSQL Identity Column - GENERATED AS ALWAYS example

第三，通过为color_id和color_name列提供值来插入新行：

INSERT INTO color (color_id, color_name)
VALUES (2, 'Green');

PostgreSQL 发出以下错误：

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

要修复该错误，您可以使用OVERRIDING SYSTEM VALUE子句，如下：

INSERT INTO color (color_id, color_name)
OVERRIDING SYSTEM VALUE 
VALUES(2, 'Green');

PostgreSQL identity column - OVERRIDING SYSTEM VALUE example

或者改用GENERATED BY DEFAULT AS IDENTITY。

B) GENERATED BY DEFAULT AS IDENTITY 例子

首先，删除 color表并重新创建它。这次我们改用GENERATED BY DEFAULT AS IDENTITY：

DROP TABLE color;

CREATE TABLE color (
    color_id INT GENERATED BY DEFAULT AS IDENTITY,
    color_name VARCHAR NOT NULL
);

其次，向color表中插入一行：

INSERT INTO color (color_name)
VALUES ('White');

它按预期工作。

第三，插入另一行，其中包含color_id列的值：

INSERT INTO color (color_id, color_name)
VALUES (2, 'Yellow');

与前面使用GENERATED ALWAYS AS IDENTITY约束的示例不同，上面的语句工作得很好。

C) 序列选项示例

由于GENERATED AS IDENTITY约束使用SEQUENCE对象，因此您可以为系统生成的值指定序列选项。

例如，您可以指定起始值和增量，如下所示：

DROP TABLE color;

CREATE TABLE color (
    color_id INT GENERATED BY DEFAULT AS IDENTITY 
    (START WITH 10 INCREMENT BY 10),
    color_name VARCHAR NOT NULL
);

在此示例中，系统生成的color_id列值从 10 开始，增量值也是 10。

首先，在color表中插入一个新行：

INSERT INTO color (color_name)
VALUES ('Orange');

color_id列的起始值为 10，如下所示：

SELECT * FROM color;

PostgreSQL identity column - sequence options example

其次，在color表中插入另一行：

INSERT INTO color (color_name)
VALUES ('Purple');

由于增量选项，第二行的color_id值为 20。

SELECT * FROM color;

PostgreSQL identity column - increment example

向现有表添加标识列

您可以使用以下形式的ALTER TABLE语句向现有表添加标识列：

ALTER TABLE table_name 
ALTER COLUMN column_name 
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY { ( sequence_option ) }

让我们看下面的例子。

首先，创建一个名为shape的新表：

CREATE TABLE shape (
    shape_id INT NOT NULL,
    shape_name VARCHAR NOT NULL
);

其次，将该shape_id列更改为标识列：

ALTER TABLE shape 
ALTER COLUMN shape_id ADD GENERATED ALWAYS AS IDENTITY;

请注意，shape_id需要有NOT NULL约束，以便可以将其更改为标识列。否则，您将收到如下错误：

ERROR:  column "shape_id" of relation "shape" must be declared NOT NULL before identity can be added
SQL state: 55000

以下命令在psql工具中描述了shape表：

\d shape

它返回以下输出，这正是我们所期望的：

img

更改标识列

您可以使用以下ALTER TABLE语句更改现有标识列的特征：

ALTER TABLE table_name 
ALTER COLUMN column_name 
{ SET GENERATED { ALWAYS| BY DEFAULT } | 
  SET sequence_option | RESTART [ [ WITH ] restart ] }

例如，以下语句将shape表的shape_id列更改为GENERATED BY DEFAULT：

ALTER TABLE shape
ALTER COLUMN shape_id SET GENERATED BY DEFAULT;

以下命令在psql工具中描述了shape表的结构：

\d shape

img

从输出中可以看到，shape_id列从GENERATED ALWAYS更改为了GENERATED BY DEFAULT。

删除 GENERATED AS IDENTITY 约束

以下语句从现有表中删除GENERATED AS IDENTITY约束：

ALTER TABLE table_name 
ALTER COLUMN column_name 
DROP IDENTITY [ IF EXISTS ]

例如，您可以从shape表的shape_id列中删除GENERATED AS IDENTITY约束列，如下所示：

ALTER TABLE shape
ALTER COLUMN shape_id
DROP IDENTITY IF EXISTS;

在本教程中，您学习了如何使用 PostgreSQL 标识列以及如何使用GENERATED AS IDENTITY约束来管理它。

搜索

分类

标签