PostgreSQL 标识列


PostgreSQL 标识列

在本节中,我们将了解PostgreSQL IDENTITY 和 GENERATED 约束的工作,它们用于为特定列生成 PostgreSQL 身份列,更改身份列的示例,并通过示例了解Sequence 选项的工作。

我们还将看到向当前表添加标识列并使用ALTER TABLE命令修改标识列以删除GENERATED AS IDENTITY约束的示例。

什么是 PostgreSQL 标识列?

从PostgreSQL版本 10 开始,PostgreSQL 开发人员宣布了一个名为GENERATED AS IDENTITY的新约束,用于自动为列分配唯一编号。

PostgreSQL Identity 列的语法

PostgreSQL GENERATED AS IDENTITY 约束的语法如下:

  1. column_name 类型 GENERATED { 总是 | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

在上面的Syntax中,我们有以下几点,在实时示例中使用时需要记住:

  • 所述类型参数可以是SMALLINT,INT,BIGINT
  • 所述GENERATED ALWAYS被用于要求的PostgreSQL不断创建用于标识列的值。
  • 如果我们尝试将值更新或插入到GENERATED ALWAYS AS IDENTITY 列中, PostgreSQL 将发生错误。
  • 但是如果我们为插入或更新提供一个值,则GENERATED BY DEFAULT用于告诉 PostgreSQL 为标识列创建一个值。
  • 为了使用系统生成的值,PostgreSQL 将使用特定的值插入到标识列中。

PostgreSQL 标识列示例

让我们看不同的例子来理解PostgreSQL 标识列是如何工作的。

  • GENERATED ALWAYS 示例

我们正在使用 CREATE 命令的帮助创建一个新表作为Vegetable\,并使用 INSERT 命令插入一些值。

要将Vegetable\创建到组织数据库中,我们使用CREATE命令。

蔬菜\表包含两列如veggie_id和Veggie_name栏,在这里我们使用的Veggie_id作为标识列:

CREATE TABLE Vegetable  (  
Veggie_id INT GENERATED ALWAYS AS IDENTITY,  
Veggie_name VARCHAR NOT NULL  
);

输出

执行上述命令时,我们将收到以下消息,显示Vegetable\表已成功创建到Organization数据库中。

PostgreSQL 标识列

Vegetable成功创建表,我们将插入一些值与INSERT命令的帮助。

INSERT INTO Vegetable(veggie_name)  
VALUES ('Sweet Potato');

执行上述命令后,我们会得到如下消息窗口,显示该值已成功插入到Vegetable\表中。

PostgreSQL 标识列

我们知道Veggie_id列具有GENERATED AS IDENTITY 约束,这就是 PostgreSQL 为其创建一个值的原因,我们可以在以下命令中看到:

SELECT * FROM Vegetable;

输出

成功执行上述命令后,我们将得到以下输出,其中显示了Vegetable\表中的所有数据:

PostgreSQL 标识列

现在,我们将通过为 Veggie_id 和 Veggie_name 列提供值来插入一个新行:

INSERT INTO Vegetable(Veggie_id, Veggie_name)  
VALUES (2,'Carrot');

输出

执行上述命令后,Postgresql 引发以下错误:我们无法将值插入到 veggie_id 列中,因为Veggie_id 列是一个标识列并被描述为 GENERATED ALWAYS。

PostgreSQL 标识列

我们可以使用OVERRIDING SYSTEM VALUE子句来解决以下命令中的上述错误:

INSERT INTO Vegetable(Veggie_id, Veggie_name)  
OVERRIDING SYSTEM VALUE   
VALUES(2, 'Carrot');

输出

执行上述命令后,我们将得到以下消息窗口,显示指定的值已成功插入到Vegetable\表中。

PostgreSQL 标识列

注意:代替 GENERATED ALWAYS AS IDENTITY,我们可以使用 GENERATED BY DEFAULT AS IDENTITY。

GENERATED BY DEFAULT AS IDENTITY 示例

让我们看看示例示例,以了解默认情况下生成的身份是如何工作的。

步骤1

首先,我们将删除Vegetable\表并使用GENERATED BY DEFAULT AS IDENTITY代替GENERATED ALWAYS AS IDENTITY来重建它:

DROP TABLE Vegetable;

输出

执行上述命令后,我们将看到以下消息窗口,表明Vegetable\ Table 已成功删除。

PostgreSQL 标识列

第2步

现在,我们将使用 GENERATED BY DEFAULT AS IDENTITY 创建一个类似的表,如以下命令所示:

CREATE TABLE Vegetable  (  
Veggie_id INT GENERATED BY DEFAULT AS IDENTITY,  
Veggie_name VARCHAR NOT NULL  
);

输出

在执行上述命令时,我们将看到以下消息窗口,显示Vegetable\表已成功创建。

PostgreSQL 标识列

Step3

再次成功创建Vegetable表后,我们将使用INSERT命令插入一些值,如下所示:

INSERT INTO Vegetable(Veggie_name)   
VALUES('Onion');

输出

执行上述命令后,我们会得到如下消息窗口,显示该值已成功插入到Vegetable\表中。

PostgreSQL 标识列

第四步

之后,我们将更多的值插入到具有Veggie_idVegetable\表中,如以下命令所示:

INSERT INTO Vegetable(Veggie_id, Veggie_name)   
VALUES(2, 'Cabbage'),  
(3, 'Broccoli'),  
(4, 'Tomato'),  
(5, 'Avocado');

输出

执行上述命令后,我们会得到如下消息窗口,显示这四个值已成功插入到Vegetable\表中。

PostgreSQL 标识列

注意:如果我们比较这两个命令,我们可以看到 GENERATED ALWAYS AS IDENTITY 约束的使用发生错误,而 GENERATED BY DEFAULT AS IDENTITY 约束执行成功并且没有引发任何错误。

  • 序列选项示例

我们可以为系统生成的值定义序列选项,因为GENERATED AS IDENTITY约束使用 SEQUENCE 对象。

让我们看一个例子来了解 Sequence 选项的工作。

在下面的命令中,我们描述了起始值和增量值,如下所示:

DROP TABLE Vegetable;

输出

执行完上面的命令后,我们会得到如下消息窗口,显示指定的表已经删除成功。

PostgreSQL 标识列

现在,我们将再次创建同一个表 ( Vegetable\ ),但这一次,我们使用Start 和 Increment参数,如下面的命令所示:

CREATE TABLE Vegetable (  
Veggie_id INT GENERATED BY DEFAULT AS IDENTITY   
(START WITH 5 INCREMENT BY 5),  
Veggie_name VARCHAR NOT NULL);

输出

执行上述命令时,我们将得到以下消息窗口,显示Vegetable\表已成功创建到Organization数据库中。

PostgreSQL 标识列

另外,在上述命令时,系统生成的值对于Veggie_id柱开始5,增量值也是5

现在,我们将在INSERT命令的帮助下将一个新值插入到Vegetable\表中:

INSERT INTO Vegetable(Veggie_name)   
VALUES('Pumpkin');

输出

执行上述命令后,我们将得到以下输出:

PostgreSQL 标识列

创建并插入Vegetable\表的值后,我们将使用SELECT命令返回Vegetable表的所有行:

SELECT * FROM Vegetable;

输出

在成功执行上述命令,我们会得到以下输出,这显示起始值Veggie_id列是5

PostgreSQL 标识列

成功执行上述命令后,我们将在蔬菜\表中插入更多行:

INSERT INTO Vegetable(Veggie_name)   
VALUES('Zucchini'),  
('White Button Mushroom'),  
('Cauliflower');

输出

执行上述命令后,我们将得到以下消息窗口,显示指定的值已成功插入到Vegetable\表中。

PostgreSQL 标识列

第二行的Veggie_id值为 10,因为我们在创建Vegetable\表时使用了增量选项:

SELECT * FROM Vegetable;

输出

成功执行上述命令后,我们将得到以下输出,其中显示了Vegetable\表中的所有现有数据:

PostgreSQL 标识列

向当前表添加标识列

我们可以借助ALTER TABLE 命令的以下语法将标识列添加到当前表中:

向当前表添加标识列的语法

下图用于向现有表添加标识列:

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

让我们看一个示例以了解以下内容:

要将标识列添加到现有表,我们必须按照以下步骤操作:

Step1:新建表

我们正在使用 CREATE 命令的帮助创建一个新表作为结构\

要在组织数据库中创建结构\,我们使用CREATE命令。

Purchase_details\表包含两列,如Str_id和Str_name

CREATE TABLE Structure (  
    Str_id INT NOT NULL,  
    Str_name VARCHAR NOT NULL  
);

输出

执行上述命令后,我们将看到以下消息窗口,其中显示Structure\表已成功创建到Organization数据库中。

PostgreSQL 标识列

Step2:将Str_id列修改为identity列

在以下命令中,我们将使用 ALTER Table 命令将Str_id列更改为标识列:

ALTER TABLE Structure  
ALTER COLUMN Str_id ADD GENERATED ALWAYS AS IDENTITY;

输出

执行完上面的命令后,我们会得到如下消息窗口,显示Structure表已经修改成功。

PostgreSQL 标识列

注意:Str_id 列需要有 NOT NULL 约束;因此,可以将其修改为标识列。否则,PostgreSQL 会出现如下错误:

ERROR:  column "str_id" of relation "structure" must be declared NOT NULL before an identity can be added  
SQL state: 55000

在 SQL Shell(psql) 中描述结构表

SQL shell(psql)工具中描述Structure\表,我们可以使用下面的命令,但是在使用describe命令之前,我们将按照以下流程进行:

  • 首先,我们将在我们的本地系统中打开psql,我们将连接到我们要创建表的数据库。
  • 为了连接组织数据库,我们将输入以下命令:
postgres=# \c Organization

输出

执行上述命令后,我们将得到以下输出:

PostgreSQL 标识列

  • 现在,我们将输入以下命令将结构表描述到组织中
\d Structure

输出

在执行上述命令时,我们将检索以下输出,这是我们对Structure\表的期望:

PostgreSQL 标识列

更改标识列

ALTER TABLE 命令还用于更改当前标识列的功能,如下图所示:

更改标识列的语法

以下语法用于更改标识列:

Alter table table_name   
Alter column  column_name   
{ SET GENERATED { ALWAYS| BY DEFAULT } |   
 SET sequence_option | RESTART [ [ WITH] restart ] }

让我们看一个示例,以更好地理解如何使用 ALTER TABLE 命令更改标识列:

在下面的命令中,我们使用GENERATED BY DEFAULT约束修改了Structure\表的Str_id列:

ALTER TABLE Structure   
ALTER COLUMN Str_id SET GENERATED BY DEFAULT;

输出

执行完上面的命令后,我们会得到如下消息窗口,显示Structure表已经修改成功。

PostgreSQL 标识列

并描述psql中Structure\表的设计;我们将使用与上面类似的命令:

\d Structure

输出

执行上述语句后,我们将得到以下输出,这表明Str_id列已从GENERATED ALWAYS修改为GENERATED BY DEFAULT

PostgreSQL 标识列

删除 GENERATED AS 身份约束

要从当前表中删除 GENERATED AS IDENTITY 约束,我们将使用以下语法:

删除生成的 AS 身份约束的语法

下图用于从指定表中删除 Generated as Identity Constraint:

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

让我们看一个例子,它显示了删除生成的 AS 身份约束的工作。

在以下命令中,我们将从Structure\表的Str_id列中删除 GENERATED AS IDENTITY 约束列:

ALTER TABLE Structure  
ALTER COLUMN Str_id  
DROP IDENTITY IF EXISTS;

输出

执行完上面的命令后,我们会得到如下消息窗口,显示Structure表的GENERATED AS IDENTITY约束列已经成功去除。

PostgreSQL 标识列

我们将再次使用以下命令来检查结构\表的Str_id列中的GENERATED AS IDENTITY 约束列是否已在 SQL shell (PSQL) 中成功删除:

\d structure

输出

执行上述命令后,我们会得到如下结果,显示GENERATED AS IDENTITY 约束列已从Structure表中删除。

PostgreSQL 标识列

概述

PostgreSQL 身份列部分,我们学习了以下主题:

  • 我们通过使用GENERATED AS IDENTITY学习了PostgreSQL Identity Column的工作
  • 我们已经看到了GENERATED ALWAYS 约束GENERATED BY DEFAULT AS IDENTITY 约束的例子
  • 我们通过一些示例了解了Sequence 选项的工作原理
  • 我们还可以使用 ALTER TABLE 命令的帮助向现有表添加标识列。
  • 在本节中,我们还将了解如何使用 ALTER TABLE 命令修改 Identity 列。
  • 我们使用ALTER TABLE命令删除了 GENERATED AS IDENTITY 约束。