PostgreSQL表标识列教程

本文学习使用 GENERATED AS IDENTITY 约束创建 PostgreSQL 表的标识列 。

标识列介绍

PostgreSQL 10 引入新的特性：GENERATED AS IDENTITY 约束，可以给列自动赋唯一值，它是非常好用serial 列的变体。语法如下：

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

• type 可以是 SMALLINT, INT, or BIGINT.
• GENERATED ALWAYS 指示PostgreSQL对标识列总是生成值。如果尝试插入或更新 GENERATED ALWAYS AS IDENTITY 列，操作会报错。
• GENERATED BY DEFAULT 也指示PostgreSQL对标识列总是生成值。但允许你对标识列插入或更新。

PostgreSQL 允许一个表拥有一个或多个标识列，如：serial ，GENERATED AS IDENTITY 约束也在内部使用 SEQUENCE 对象。

标识列示例

1）GENERATED ALWAYS AS IDENTITY

创建color表，其中 color_id 为标识列：

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

INSERT INTO color(color_name) VALUES ('Red');
SELECT * FROM color;

返回结果：

color_id	color_name
1	Red

如果我们这样插入数据会报错：

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

SQL 错误 [428C9]: 错误: 无法插入到列"color_id"
Detail: 列"color_id"是定义为GENERATED ALWAYS的标识列.
Hint: 使用OVERRIDING SYSTEM VALUE覆盖.

我们按照提示修改SQL：

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

返回结果：

color_id	color_name
1	Red
2	Green

2）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
);

我们插入示例数据：

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

与前面示例不同，这两条语句都可以执行成功。

3）Sequence options

因为 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使用序列填充值，下面插入数据：

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

select * from color 

查看返回结果：

color_id	color_name
10	Purple
20	Orange

维护标识列

下面介绍如何对已存在的表增加标识列，修改和删除标识列。

SELECT relname sequence_nameFROM pg_class WHERE relkind = 'S';

上面SQL查看已经存在的序列，前面已经提及 GENERATED AS IDENTITY 底层使用序列对象。

1）增加标识列

可以使用 ALTER TABLE 语句对已有表增加标识列，语法如下：

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

请看示例：

# 创建表CREATE TABLE shape (    shape_id INT NOT NULL,    shape_name VARCHAR NOT NULL);# 修改增加标识列ALTER TABLE shape ALTER COLUMN shape_id ADD GENERATED ALWAYS AS IDENTITY;

需要提醒的是，shape_id 字段必须有 NOT NULL 约束，否则会报错。

2）修改标识列

也可以对已经存在的标识列进行修改，语法如下：

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

举例，对上面示例的标识列进行修改：

ALTER TABLE shapeALTER COLUMN shape_id SET GENERATED BY DEFAULT;

从 ALWAYS 修改为 BY DEFAULT。

3）删除标识列

下面语句删除标识列：

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

示例：

ALTER TABLE shapeALTER COLUMN shape_idDROP IDENTITY IF EXISTS;

上面示例删除shape_id 的标识列约束。