PostgreSQL 数组：函数、类型、示例
Juniper Willow 什么是 PostgreSQL 数组？
在 PostgreSQL 中，我们可以将列定义为有效数据类型的数组。数据类型可以是内置的、用户定义的或枚举类型。除此之外，数组在 PostgreSQL 中也扮演着重要的角色。
每个相应的 PostgreSQL 数据类型都有一个相关的数组类型。例如，整数数据类型具有 integer[] 数组类型，字符数据类型具有 character[] 数组类型，依此类推。
创建 PostgreSQL 数组
在下面的示例中，我们将创建一个名为 Employees 的表，其中 contact 列定义为文本数组
CREATE TABLE Employees ( id int PRIMARY KEY, name VARCHAR (100), contact TEXT [] );
该命令应该成功运行。
插入 PostgreSQL 数组值
现在，让我们插入值到上面的表中
INSERT INTO Employees
VALUES
(
1,
'Alice John',
ARRAY [ '(408)-743-9045',
'(408)-567-7834' ]
);
插入操作应该成功运行。
第三列，即 contact，其值已作为数组插入。这是通过使用 ARRAY 构造函数实现的。
在此示例中，我们将其括在方括号 [] 中。员工 Alice John 有两个联系方式。
如下所示，我们仍然可以使用花括号 {}
INSERT INTO Employees
VALUES
(
2,
'Kate Joel',
'{"(408)-783-5731"}'
),
(
3,
'James Bush',
'{"(408)-745-8965","(408)-567-78234"}'
);
该命令应该成功运行。
以上语句会将两行插入 Employees 表。使用花括号时，数组用单引号 (‘) 括起来，而文本数组项用双引号 (“) 括起来。
查询数组数据
要查询数组的元素，我们使用 SELECT 语句。
要查看我们的 Employees 表的内容，我们运行以下命令
SELECT * FROM Employees;
这将返回以下内容
数组列（即 contact）的元素已包含在花括号 {} 中。
要访问数组元素本身，我们在方括号 [] 中添加一个下标。数组中的第一个元素位于位置 1。
例如，我们需要获取员工的姓名以及联系方式只有一个的员工的第一个联系方式。我们可以将其访问为 contact[1]。
让我们看看这个
SELECT name, contact[1] FROM Employees;
这将返回以下内容
我们可以使用 SELECT 语句以及 WHERE 子句根据数组列过滤行。
例如，要查看第二个联系方式为 (408)-567-78234 的员工，我们可以运行以下命令
SELECT name FROM Employees WHERE contact [ 2 ] = '(408)-567-78234';
这将返回以下内容
修改 PostgreSQL 数组
您可以更新数组的全部或单个元素。
这是 Employees 表的内容
让我们更新 id 为 3 的员工 James Bush 的第二个电话号码
运行以下命令
UPDATE Employees SET contact [ 2 ] = '(408)-589-89347' WHERE id = 3;
该命令应该成功运行
让我们查询表以检查更改是否成功
更改已成功。
在 PostgreSQL 数组中搜索
当前，我们的 Employees 表如下
假设我们需要知道谁拥有联系方式 (408)-783-5731，而不考虑其在联系方式数组中的位置，我们可以使用 ANY() 函数，如下所示
SELECT name, contact FROM Employees WHERE '(408)-783-5731' = ANY (contact);
这将返回以下内容
展开数组
我们可以将数组的值拆分成行。此过程称为数组展开。
在 Employees 表的示例中，有些员工的 contact 数组中有两个联系方式。我们可以将它们拆分成单独的行。
PostgreSQL 提供了 unnest() 函数，可用于此目的。
例如
SELECT name, unnest(contact) FROM Employees;
这将返回以下内容
员工 Alice John 和 James Bush 有两个联系方式。我们可以将其拆分成单独的行。
使用 pgAdmin
创建 PostgreSQL 数组
要通过 pgAdmin 完成相同的操作，请执行此操作
步骤 1) 登录您的 pgAdmin 账户。
步骤 2)
- 在左侧导航栏中，单击 bases。
- 单击“演示”按钮
步骤 3) 在查询编辑器中键入查询以创建 Employees 表
CREATE TABLE Employees ( id int PRIMARY KEY, name VARCHAR (100), contact TEXT [] );
步骤 4) 点击 Execute 按钮。
插入 PostgreSQL 数组值
步骤 1) 在查询编辑器中键入以下查询
INSERT INTO Employees
VALUES
(
1,
'Alice John',
ARRAY [ '(408)-743-9045',
'(408)-567-7834' ]
);
步骤 2) 单击“执行”按钮
步骤 3)
在查询中使用花括号
步骤 1) 在查询编辑器中键入以下查询
INSERT INTO Employees
VALUES
(
2,
'Kate Joel',
'{"(408)-783-5731"}'
),
(
3,
'James Bush',
'{"(408)-745-8965","(408)-567-78234"}'
);
步骤 2) 单击“执行”按钮
查询数组数据
步骤 1) 要查看 Employees 表的内容，请在查询编辑器中键入以下查询
SELECT * FROM Employees;
步骤 2) 单击“执行”按钮
它应该返回以下内容
步骤 3) 查看员工的第一个联系方式
- 在查询编辑器中键入以下查询
SELECT name, contact[1] FROM Employees;
- 点击 Execute 按钮。
它应该返回以下内容
步骤 4) 将 SELECT 语句与 WHERE 子句结合使用
- 在查询编辑器中键入以下命令
SELECT name FROM Employees WHERE contact [ 2 ] = '(408)-567-78234';
- 点击 Execute 按钮。
它应该返回以下内容
修改 PostgreSQL 数组
步骤 1) 要更新 id 为 3 的用户的第二个联系方式，请运行以下命令
UPDATE Employees SET contact [ 2 ] = '(408)-589-89347' WHERE id = 3;
步骤 2) 单击“执行”按钮。
步骤 3)
1. 在查询编辑器中键入以下命令以检查更改是否成功
SELECT * FROM Employees;
2. 单击“执行”按钮。
它应该返回以下内容
在 PostgreSQL 数组中搜索
步骤 1) 在查询编辑器中键入以下查询
SELECT name, contact FROM Employees WHERE '(408)-783-5731' = ANY (contact);
步骤 2) 单击“执行”按钮。
它应该返回以下内容
展开数组
步骤 1) 在查询编辑器中键入以下查询
SELECT name, unnest(contact) FROM Employees;
步骤 2) 单击“执行”按钮。
它应该返回以下内容
摘要
- PostgreSQL 允许我们将表列定义为数组类型。
- 数组必须是有效的数据类型，例如整数、字符或用户定义的类型。
- 要将值插入数组列，我们使用 ARRAY 构造函数。
- 如果数组列的同一行中有多个元素，第一个元素位于位置 1。
- 可以通过在方括号 [] 中传递下标来访问每个值。
- 可以使用 SELECT 语句检索数组的元素。
- 数组列的值可以括在方括号 [] 或花括号 {} 中。
- 我们可以使用 ANY() 函数搜索数组列值。
