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_idcolor_name
1Red

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

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_idcolor_name
1Red
2Green

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_idcolor_name
10Purple
20Orange

维护标识列

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

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 的标识列约束。

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PostgreSQL是一种开源的关系型数据库管理系统(RDBMS),它支持广泛的数据存储和处理需求。下面是关于PostgreSQL存储的介绍: 1. 数据库PostgreSQL使用数据库来组织和存储数据。一个数据库可以包含多个和其他相关对象,如视图、索引、函数等。 2. 数据库中的基本存储单元,用于存储数据。每个由一组和行组成,定义了中的数据类型,行则示具体的数据记录。 3. 中的一个字段,用于存储特定类型的数据。每个都有一个名称和数据类型,例如整数、字符串、日期等。 4. 行:行是中的一个记录,包含了一组相关的数据。每一行都有一个唯一的标识符,称为主键,用于唯一地标识该行。 5. 索引:索引是一种数据结构,用于提高数据库查询的性能。它可以加快数据的查找速度,类似于书籍的目录。 6. 视图:视图是一个虚拟,它是基于一个或多个的查询结果生成的。视图可以简化复杂的查询操作,并提供了一种安全访问数据的方式。 7. 存储过程:存储过程是一段预先编译好的代码,可以在数据库中执行。它可以接受参数并返回结果,用于实现复杂的业务逻辑。 8. 触发器:触发器是与相关联的一段代码,它在上的特定操作(如插入、更新、删除)发生时自动执行。触发器可以用于实现数据约束和业务逻辑。 9. 外键:外键是之间的关联关系,用于保持数据的完整性。它定义了一个中的与另一个中的主键之间的关系。 10. 备份和恢复:PostgreSQL提供了备份和恢复机制,用于保护数据免受意外损坏或丢失。可以使用备份文件还原数据库到之前的状态。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值