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 ) ]

在这个语法中:

  • 类型可以是SMALLINTINTBIGINT
  • 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_idcolor_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约束来管理它。