Skip to main content
 首页 » 数据库

PostgreSQL表标识列教程

2022年07月19日163me-sa

本文学习使用 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 的标识列约束。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/120273789
阅读延展