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数据库中。

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

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

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

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

SELECT * FROM Vegetable;

输出

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

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

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

输出

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

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

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

输出

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

注意：代替 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 已成功删除。

第2步

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

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

输出

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

Step3

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

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

输出

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

第四步

之后，我们将更多的值插入到具有Veggie_id的Vegetable\表中，如以下命令所示：

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

输出

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

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

• 序列选项示例

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

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

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

DROP TABLE Vegetable;

输出

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

现在，我们将再次创建同一个表 ( 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数据库中。

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

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

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

输出

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

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

SELECT * FROM Vegetable;

输出

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

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

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

输出

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

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

SELECT * FROM Vegetable;

输出

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

向当前表添加标识列

我们可以借助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数据库中。

Step2：将Str_id列修改为identity列

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

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

输出

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

注意：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

输出

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

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

\d Structure

输出

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

更改标识列

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表已经修改成功。

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

\d Structure

输出

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

删除 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约束列已经成功去除。

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

\d structure

输出

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

概述

在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 约束。