PostgreSQL 标识列

在本节中，我们将理解 PostgreSQL IDENTITY 和 GENERATED 约束 的工作原理，该约束用于为特定列生成 PostgreSQL 标识列，以及修改标识列的示例，并结合示例理解 序列选项 的工作原理。

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

什么是 PostgreSQL 标识列？

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

PostgreSQL 标识列的语法

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

在上面的语法中，我们有以下几点，在使用实际示例时需要记住：

• type 参数 可以是 SMALLINT, INT, 或 BIGINT。
• GENERATED ALWAYS 用于要求 PostgreSQL 始终为标识列创建值。
• 如果我们尝试更新或插入 GENERATED ALWAYS AS IDENTITY 列 的值，PostgreSQL 将会报错。
• 但是，如果我们提供一个值用于插入或更新，GENERATED BY DEFAULT 将用于告知 PostgreSQL 为标识列创建值。
• 并且为了使用系统生成的值，PostgreSQL 将使用该特定值插入到标识列中。

PostgreSQL 标识列示例

让我们看几个不同的示例来理解 PostgreSQL 标识列 的工作原理。

• GENERATED ALWAYS 示例

我们将使用 CREATE 命令创建一个名为 Vegetable 的新表，并使用 INSERT 命令插入一些值。

要将 Vegetable 创建到 Organization 数据库 中，我们使用 CREATE 命令。

Vegetable 表包含两列：veggie_id 和 Veggie_name 列，其中我们将 Veggie_id 用作 标识列：

输出

执行上述命令后，我们将收到以下消息，表明 Vegetable 表已成功创建到 Organization 数据库中。

当 Vegetable 表成功创建后，我们将使用 INSERT 命令向其中插入一些值。

输出

实现上述命令后，我们将收到以下消息窗口，表明一个值已成功插入到 Vegetable 表中。

如我们所知，Veggie_id 列具有 GENERATED AS IDENTITY 约束，因此 PostgreSQL 会为其创建值，如下面的命令所示：

输出

成功执行上述命令后，我们将获得以下输出，显示 Vegetable 表中存在的所有数据。

现在，我们将通过提供 Veggie_id 和 Veggie_name 列的值来插入新行。

输出

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

我们可以使用 OVERRIDING SYSTEM VALUE 子句来解决上述错误，如下面的命令所示：

输出

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

注意：在 GENERATED ALWAYS AS IDENTITY 的位置，我们可以使用 GENERATED BY DEFAULT AS IDENTITY。

GENERATED BY DEFAULT AS IDENTITY 示例

让我们看一些示例来理解 Generated by default as identity 的工作原理。

步骤 1

首先，我们将删除 Vegetable 表，并使用 GENERATED BY DEFAULT AS IDENTITY 替换 GENERATED ALWAYS AS IDENTITY 来重新构建它。

输出

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

步骤 2

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

输出

执行上述命令后，我们将收到以下消息窗口，表明 Vegetable 表已成功创建。

步骤 3

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

输出

实现上述命令后，我们将收到以下消息窗口，表明一个值已成功插入到 Vegetable 表中。

步骤 4

之后，我们将 Veggie_id 作为以下命令所示的值插入到 Vegetable 表中。

输出

执行上述命令后，我们将收到以下消息窗口，表明四个值已成功插入到 Vegetable 表中。

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

• 序列选项示例

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

让我们看一个示例来查看序列选项的工作原理。

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

输出

执行上述命令后，我们将收到以下消息窗口，表明指定的表已成功 **删除**。

现在，我们将再次创建相同的表（Vegetable），但这次我们将使用 Start 和 Increment 参数，如下面的命令所示：

输出

执行上述命令后，我们将收到以下消息窗口，表明 Vegetable 表已成功创建到 Organization 数据库中。

在上面的命令中，Veggie_id 列的 **系统生成值** 从 5 开始，**增量值** 也为 5。

现在，我们将使用 INSERT 命令 向 Vegetable 表中插入一个新值。

输出

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

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

输出

成功执行上述命令后，我们将获得以下输出，显示 Veggie_id 列的 **起始值** 为 5。

一旦上述命令成功执行，我们将向 Vegetable 表中插入更多行。

输出

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

第二个行的 Veggie_id 值为 10，因为我们在创建 Vegetable 表时使用了 increment 选项。

输出

成功执行上述命令后，我们将获得以下输出，显示 Vegetable 表中存在的所有数据。

向现有表添加标识列

我们可以使用以下 ALTER TABLE 命令 的语法向现有表添加标识列。

向现有表添加标识列的语法

以下图示用于向现有表添加标识列。

让我们看一个示例来理解这一点。

要向现有表添加标识列，我们必须遵循以下步骤：

步骤 1：创建新表

我们将使用 CREATE 命令创建一个名为 Structure 的新表。

要将 Structure 创建到 Organization 数据库 中，我们使用 CREATE 命令。

Purchase_details 表包含两列，如 Str_id 和 Str_name。

输出

执行上述命令后，我们将收到以下消息窗口，表明 Structure 表已成功创建到 Organization 数据库中。

步骤 2：修改 Str_id 列为标识列

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

输出

实现上述命令后，我们将收到以下消息窗口，表明 Structure 表已成功更改。

注意：Str_id 列需要具有 NOT NULL 约束，因此可以将其修改为标识列。否则，PostgreSQL 将会报错。

在 SQL Shell (psql) 中描述 Structure 表

要在 SQL shell(psql) 工具中描述 Structure 表，我们可以使用以下命令，但在使用 describe 命令之前，我们将遵循以下过程：

• 首先，我们将打开本地系统中的 psql，并连接到我们要创建表的数据库。
• 要连接 Organization 数据库，我们将输入以下命令：

输出

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

• 现在，我们将输入以下命令来描述 Organization 中的 Structure 表。

输出

执行上述命令后，我们将获得以下输出，这正是我们对 Structure 表的预期。

修改标识列

ALTER TABLE 命令也用于修改当前标识列的特性，如下面的图示所示。

修改标识列的语法

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

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

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

输出

实现上述命令后，我们将收到以下消息窗口，表明 Structure 表已成功 **修改**。

要在 psql 中描述 Structure 表的设计，我们将使用与上面类似的命令。

输出

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

删除 GENERATED AS IDENTITY 约束

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

删除 Generated AS Identity 约束的语法

以下图示用于从指定表中删除 Generated as Identity 约束。

让我们看一个示例，展示删除 Generated AS Identity Constraint 的工作原理。

在下面的命令中，我们正在从 Structure 表的 Str_id 列中删除 GENERATED AS IDENTITY 约束列。

输出

实现上述命令后，我们将收到以下消息窗口，表明 Structure 表的 GENERATED AS IDENTITY constraint 列已成功删除。

我们将再次使用以下命令来检查 GENERATED AS IDENTITY constraint 列是否已成功从 Structure 表的 Str_id 列中删除，在 SQL shell (PSQL) 中。

输出

执行上述命令后，我们将得到以下结果，表明 GENERATED AS IDENTITY constraint 列已从 Structure 表中删除。

概述

在 PostgreSQL 标识列 部分，我们学习了以下主题：

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