PostgreSQL 高级数据类型

介绍

在本实验中，你将探索 PostgreSQL 的高级数据类型，重点关注 JSON/JSONB、数组和 UUID。你将学习如何存储、查询和操作这些类型中的数据。

实验开始时，将演示如何存储和查询 JSON 和 JSONB 数据，包括创建一个带有 JSONB 列的表，插入 JSON 数据，以及使用 -> 和 ->> 等操作符提取特定值。然后，你将继续学习数组列和 UUID。

存储和查询 JSON 和 JSONB 数据

在此步骤中，你将学习如何在 PostgreSQL 中存储和查询 JSON 和 JSONB 数据。PostgreSQL 提供了两种存储 JSON 数据的数据类型：JSON 和 JSONB。JSON 数据类型存储 JSON 输入文本的精确副本，而 JSONB 数据类型以分解的二进制格式存储 JSON 数据。JSONB 通常更受青睐，因为它在查询和索引方面提供了更好的性能。

让我们开始打开 PostgreSQL shell。首先，连接到 labex 数据库：

sudo -u postgres psql -d labex

你应该会看到 PostgreSQL 提示符：

labex=#

现在，让我们创建一个表来存储 JSONB 数据：

CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);

此 SQL 命令创建了一个名为 products 的表。该表有两个列：id（一个自动递增的整数主键）和 data（一个用于存储 JSON 数据的 JSONB 列）。

你应该会看到类似以下的输出：

CREATE TABLE

现在，让我们向 products 表插入一些数据：

INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');

这些命令向 products 表插入了两行。每一行都包含一个 JSON 对象，其中包含有关产品的信息。

对于每次插入，你应该会看到类似以下的输出：

INSERT 0 1

要查询 JSON 数据，你可以使用 -> 和 ->> 操作符。-> 操作符返回一个 JSON 对象，而 ->> 操作符将 JSON 值作为文本返回。

例如，要检索第一个产品的名称，你可以使用以下查询：

SELECT data ->> 'name' FROM products WHERE id = 1;

此命令从 products 表的 data 列中选择与键 name 相关联的值，其中 id 为 1。->> 操作符确保结果以文本形式返回。

你应该会看到类似以下的输出：

  ?column?
----------
 Laptop
(1 row)

你也可以查询嵌套的 JSON 对象。例如，要检索第一个产品的第一个特性，你可以使用以下查询：

SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;

此命令首先从 data 列中选择 features 数组，然后从数组中选择索引为 0 的元素。->> 操作符确保结果以文本形式返回。

你应该会看到类似以下的输出：

  ?column?
----------
 16GB RAM
(1 row)

你还可以使用 @> 操作符来检查 JSON 对象是否包含特定的键值对。例如，要查找价格为 75 的所有产品，你可以使用以下查询：

SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';

此命令从 products 表中选择所有行的名称，其中 data 列包含一个 price 键且值为 75 的 JSON 对象。

你应该会看到类似以下的输出：

  ?column?
----------
 Keyboard
(1 row)

太棒了！我们已成功创建了第一个带有 JSONB 数据的表，并学习了如何查询它。在下一步中，我们将继续使用此表来添加更高级的功能。

向现有表添加数组列

在此步骤中，你将学习如何在 PostgreSQL 中添加和操作数组列。我们将通过添加一个数组列来存储标签来扩展我们现有的 products 表。数组列允许你在单个列中存储相同数据类型的多个值，这对于存储标签、类别或特性等项目列表非常有用。

由于我们已经连接到数据库并拥有上一步创建的 products 表，让我们向现有表添加数组列：

ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];

这些命令向我们现有的 products 表添加了两个新列：name（一个字符串）和 tags（一个字符串数组）。TEXT[] 数据类型指定 tags 列是文本值数组。

对于每个 alter 命令，你应该会看到类似以下的输出：

ALTER TABLE

现在，让我们更新现有数据并插入包含新列的新数据：

UPDATE products SET name = data ->> 'name' WHERE id = 1;
UPDATE products SET name = data ->> 'name' WHERE id = 2;
UPDATE products SET tags = ARRAY['electronics', 'computers', 'portable'] WHERE id = 1;
UPDATE products SET tags = ARRAY['electronics', 'accessories', 'input'] WHERE id = 2;

这些命令使用从 JSONB 数据中提取的名称和标签信息以及新的数组值来更新我们现有的产品。

对于每次更新，你应该会看到类似以下的输出：

UPDATE 1

要查询数组数据，你可以使用数组索引。PostgreSQL 中的数组索引从 1 开始。

例如，要检索第一个产品的第一个标签，你可以使用以下查询：

SELECT tags[1] FROM products WHERE id = 1;

此命令从 products 表的 tags 数组中选择索引为 1 的元素，其中 id 为 1。

你应该会看到类似以下的输出：

   tags
-----------
 electronics
(1 row)

你还可以使用 UNNEST 函数将数组展开为一组行。

例如，要检索所有产品的全部标签，你可以使用以下查询：

SELECT name, UNNEST(tags) AS tag FROM products;

此命令从 tags 数组中选择 name 和每个单独的 tag，为每个标签创建一个新行。

你应该会看到类似以下的输出：

  name   |     tag
---------+-------------
 Laptop  | electronics
 Laptop  | computers
 Laptop  | portable
 Keyboard| electronics
 Keyboard| accessories
 Keyboard| input
(6 rows)

你可以使用 @> 操作符来检查数组是否包含特定值。

例如，要查找具有 'electronics' 标签的所有产品，你可以使用以下查询：

SELECT name FROM products WHERE tags @> ARRAY['electronics'];

此命令从 products 表中选择所有行的名称，其中 tags 数组包含值 'electronics'。

你应该会看到类似以下的输出：

  name
----------
 Laptop
 Keyboard
(2 rows)

你还可以使用 && 操作符来检查两个数组是否具有任何共同的元素。

例如，要查找与第一个产品共享任何标签的所有产品，你可以使用以下查询：

SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;

此命令从 products 表（别名为 p2）中选择所有行的名称，这些行至少有一个标签与第一个产品（别名为 p1）的标签相同，但不包括第一个产品本身。

你应该会看到类似以下的输出：

  name
----------
 Keyboard
(1 row)

完美！我们已成功向现有表添加了数组列，并学会了如何处理数组。我们的 products 表现在同时包含 JSONB 和数组数据类型，已准备好进行下一步。

添加 UUID 列并学习 UUID 生成

在此步骤中，你将学习如何在 PostgreSQL 中生成和使用 UUID（Universally Unique Identifiers）。UUID 是 128 位数字，旨在跨空间和时间保持唯一。它们通常用作数据库表中的唯一标识符，以避免在合并来自不同来源的数据时发生冲突。

由于我们将继续使用现有的会话和表，因此我们将向当前的 products 表添加一个 UUID 列来演示 UUID 功能。

首先，让我们启用提供 UUID 生成函数的 uuid 扩展：

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

此命令在 uuid-ossp 扩展不存在时创建它。

你应该会看到类似以下的输出：

CREATE EXTENSION

现在，让我们向现有的 products 表添加一个 UUID 列：

ALTER TABLE products ADD COLUMN uuid_id UUID;

此命令向我们现有的 products 表添加了一个新的 UUID 列。

你应该会看到类似以下的输出：

ALTER TABLE

现在我们可以使用 uuid_generate_v4() 函数使用 UUID 值更新现有行：

UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 1;
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 2;

这些命令使用唯一的 UUID 值更新我们现有的产品。uuid_generate_v4() 函数为每一行生成一个新的 UUID。

对于每次更新，你应该会看到类似以下的输出：

UPDATE 1

要使用 UUID 查询数据，你可以在 WHERE 子句中使用 UUID 值。由于 UUID 是随机生成的，让我们首先查看当前数据：

SELECT id, name, uuid_id FROM products;

此命令从 products 表中选择所有列，以查看生成的 UUID 以及我们的其他数据。

你应该会看到类似以下的输出（你的 UUID 将不同）：

                  id
------------------------------------
 a1b2c3d4-e5f6-7890-1234-567890abcdef
(1 row)

现在，在以下命令中使用该 UUID，将 <YOUR_UUID_HERE> 替换为你检索到的实际 UUID：

SELECT name FROM products WHERE id = '<YOUR_UUID_HERE>';

此命令从 products 表中选择 name，其中 id 与指定的 UUID 匹配。

你应该会看到类似以下的输出（取决于你选择的 UUID）：

  name
----------
 Laptop
(1 row)

最后，让我们清理我们创建的表和扩展：

DROP TABLE products;
DROP EXTENSION "uuid-ossp";

对于每个 drop 命令，你应该会看到类似以下的输出：

DROP TABLE
DROP EXTENSION

从高级类型中提取数据

在此最终步骤中，你将练习提取我们在本实验中添加到 products 表的所有高级数据类型的数据。我们的表现在包含 JSONB、数组和 UUID 列，为 PostgreSQL 的高级数据类型提供了全面的示例。

由于我们将继续使用现有的会话和表，因此我们可以立即开始处理我们在先前步骤中逐步构建的数据。

首先，让我们添加一些额外的 JSONB 数据，以使我们的提取示例更全面：

UPDATE products SET data = '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "warranty": "3 years"}' WHERE id = 1;
UPDATE products SET data = '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}, "warranty": "2 years"}' WHERE id = 2;

这些命令使用更详细的信息（包括嵌套对象和其他字段）更新我们现有的 JSONB 数据。

对于每次更新，你应该会看到类似以下的输出：

UPDATE 1

现在让我们练习从所有高级数据类型中提取数据。要从 JSONB data 列中提取数据，你可以使用 -> 和 ->> 操作符。例如，要从更新后的数据中提取品牌：

SELECT data ->> 'brand' FROM products WHERE id = 1;

此命令从 data 列中检索与键 brand 关联的值。

你应该会看到类似以下的输出：

 ?column?
----------
 Dell
(1 row)

要从 JSONB 列中提取嵌套数据，你可以链接 -> 和 ->> 操作符。例如，要提取 RAM 规格：

SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;

此命令从 specs 对象中检索与键 ram 关联的值。

你应该会看到类似以下的输出：

 ?column?
----------
 16GB
(1 row)

要从 tags（数组）列中提取数据，你可以使用数组索引，如第 2 步中所述。例如，要提取第一个产品的第一个标签：

SELECT tags[1] FROM products WHERE id = 1;

此命令从 products 表的 tags 数组中检索索引为 1 的元素，其中 id 为 1。

你应该会看到类似以下的输出：

   tags
-----------
 electronics
(1 row)

现在让我们创建一个全面的查询，从我们所有的先进数据类型中提取数据——JSONB、数组和 UUID：

SELECT
    id,
    name,
    data ->> 'brand' AS brand,
    data -> 'specs' ->> 'ram' AS ram,
    tags[1] AS first_tag,
    uuid_id
FROM products;

此命令从我们已处理过的所有高级数据类型中检索数据：整数 id、name、JSONB data 列中的 brand、嵌套的 ram 规格、tags 数组的第一个元素以及 uuid_id。

你应该会看到类似以下的输出：

   name   |  ?column?  |   tags
----------+------------+-----------
 Laptop   | Dell       | electronics
 Keyboard | Logitech   | electronics
(2 rows)

太棒了！你已成功在一个表中使用了所有三种高级 PostgreSQL 数据类型。这个全面的示例演示了如何将 JSONB、数组和 UUID 结合使用，以创建灵活且强大的数据库模式。

完成所有步骤后，你可以通过键入以下命令退出 PostgreSQL shell：

\q

你也可以选择保留该表，以便进一步试验 PostgreSQL 的高级数据类型。

总结

在本实验中，你逐步构建并使用了一个全面的 products 表，该表展示了 PostgreSQL 高级数据类型在实际应用中的作用。你首先创建了一个包含 JSONB 列的表，并学习了如何使用 -> 和 ->> 等操作符来存储和查询 JSON 数据。

接着，你通过添加数组列扩展了该表，学习了如何在单个列中存储多个值，并使用数组索引和 UNNEST 等函数进行查询。然后，你通过启用 uuid-ossp 扩展并添加 UUID 列来生成唯一标识符，从而增加了 UUID 功能。

最后，你练习了全面的数据提取技术，在复杂的查询中结合了三种高级数据类型——JSONB、数组和 UUID。这种循序渐进的方法展示了这些数据类型如何在实际数据库模式中协同工作，为现代应用程序提供了灵活性和强大的查询能力。