PostgreSQL

Appearance

数组构造函数 ​

什么是数组构造函数？ ​

数组构造函数是PostgreSQL中用于创建数组值的表达式。通过数组构造函数，你可以直接在SQL语句中创建和操作数组，而无需预先定义它们。

基本语法 ​

最简单的数组构造函数由以下部分组成：

  • 关键字 ARRAY
  • 左方括号 [
  • 数组元素（用逗号分隔）
  • 右方括号 ]

基本示例 ​

sql
SELECT ARRAY[1, 2, 3+4];

输出：

  array
---------
 {1,2,7}

在这个例子中，我们创建了一个包含三个元素的数组：1、2和7（3+4的结果）。

数组元素类型 ​

PostgreSQL会自动确定数组中元素的类型。默认情况下，它会查找所有元素的"共同类型"，使用与UNION或CASE语句相同的规则。

类型转换 ​

如果你想指定特定的类型，可以对整个数组构造函数进行类型转换：

sql
SELECT ARRAY[1, 2, 22.7]::integer[];

输出：

  array
----------
 {1,2,23}

注意22.7被转换为整数23。这种转换等效于单独转换每个元素。

多维数组 ​

你可以通过嵌套数组构造函数来创建多维数组。内部构造函数中的ARRAY关键字是可选的。

两种等效写法 ​

sql
-- 方式1：使用ARRAY关键字
SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];

-- 方式2：省略内部ARRAY关键字
SELECT ARRAY[[1,2],[3,4]];

两种方式都会产生相同的结果：

     array
---------------
 {{1,2},{3,4}}

多维数组的规则 ​

多维数组必须是矩形的，这意味着同一级别的所有内部数组必须具有相同的维度。应用于外部数组构造函数的类型转换会自动传递给所有内部构造函数。

复杂示例 ​

多维数组元素可以是任何能产生适当类型数组的表达式：

sql
CREATE TABLE arr(f1 int[], f2 int[]);

INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);

SELECT ARRAY[f1, f2, '{{9,10},{11,12}}'::int[]] FROM arr;

输出：

                     array
------------------------------------------------
 {{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}

空数组 ​

你可以创建空数组，但由于数组必须有类型，你需要显式指定类型：

sql
SELECT ARRAY[]::integer[];

输出：

 array
-------
 {}

从子查询构建数组 ​

除了直接列出元素外，还可以使用子查询的结果构建数组。这种形式使用关键字ARRAY后跟圆括号()中的子查询：

sql
SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');

输出：

                              array
------------------------------------------------------------------
 {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412}

创建二维数组 ​

sql
SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));

输出：

              array
----------------------------------
 {{1,2},{2,4},{3,6},{4,8},{5,10}}

子查询规则 ​

  • 子查询必须返回单个列
  • 如果子查询输出列是非数组类型，则生成的数组将为每行结果生成一个元素
  • 如果子查询输出列已经是数组类型，则结果数组的维度将高一个
  • 当输出列是数组时，所有子查询行必须生成相同维度的数组，否则结果将不是矩形的

实际应用示例 ​

示例1：存储多个标签 ​

sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    tags TEXT[] -- 存储多个标签
);

INSERT INTO products (name, tags) 
VALUES ('智能手机', ARRAY['电子产品', '通讯设备', '移动设备']);

SELECT * FROM products;

示例2：使用数组函数 ​

sql
-- 检查数组是否包含特定元素
SELECT name FROM products WHERE '电子产品' = ANY(tags);

-- 获取数组长度
SELECT name, array_length(tags, 1) AS tag_count FROM products;

小结 ​

数组构造函数是PostgreSQL中强大而灵活的功能，允许你：

  1. 使用简单的语法创建一维和多维数组
  2. 指定数组元素的类型
  3. 从子查询结果构建数组
  4. 在各种SQL操作中使用数组

掌握数组构造函数可以帮助你更有效地组织和处理数据，特别是在需要存储关联数据集的情况下。