PHP小码农

Appearance

ARRAY 构造函数与操作 ​

数组不只是"一列里存多个值"的容器——它有一整套构造函数与操作函数，让 SQL 能在集合层面完成过滤、展开、聚合、比较与拼接。本节的视角与类型章节不同：不再讲"数组怎么声明"，而是讲"数组怎么被函数生产出来、怎么被函数拆开、怎么与聚合互相转换"。

与「数组声明与初始化」的分工

数组声明与初始化 一章从类型视角出发，讲数组列怎么声明、字面量怎么写、维度与 array_length 的语义；本节与 数组访问与修改 互为补充，从函数视角出发，重点讲 ARRAY[] 与 ARRAY(SELECT ...) 构造器、unnest 展开、array_agg / string_agg 聚合、位置查找与集合运算符（@> / <@ / &&），以及这些函数在索引与查询改写中的用法。

前置知识

阅读本节前，建议先了解：NVL / IFNULL 兼容

基础概念 ​

数组函数可以按"数据流向"分成四类：构造（从行或字面量产出数组）、展开（把数组变回行）、查询（在数组内部查找）、变换（拼接、增删、替换）。理清这条流向，函数选择就不再靠记忆。

概念术语说明
数组构造器Array ConstructorARRAY[...]，元素是任意表达式
子查询构造器Array SubqueryARRAY(SELECT ...)，把多行折叠成数组
展开Unnestunnest(arr) 把数组变成一行一元素
集合聚合Array Aggregationarray_agg(x) 把多行折叠成数组
位置查找Positionarray_position 返回首次出现的下标
集合包含Containment@> / <@ 判断子集关系
集合重叠Overlap&& 判断是否至少共享一个元素
数组拼接Concatenationarray_cat 或 || 合并两个数组
元素追加Append / Prependarray_append / array_prepend 在尾/首插入
下标切片Slicearr[2:4] 取连续子数组

构造、展开、聚合三者的关系，是最容易绕晕的地方，先看一张"互转图"：

text
        多行                        一个数组值
   ┌──────────────┐            ┌──────────────┐
   │  1 | 2 | 3   │  array_agg │  {1,2,3}     │
   │  (行)        │ ─────────► │  (数组)      │
   │              │ ◄───────── │              │
   └──────────────┘  unnest    └──────────────┘
        ARRAY(SELECT ...) ─────────►

最小示例：

sql
SELECT
  ARRAY[1, 2, 3]                      AS built,        -- 构造器
  unnest(ARRAY[1, 2, 3])              AS unnested,     -- 展开（返回 3 行）
  array_append(ARRAY[1, 2], 3)        AS appended,     -- 追尾
  array_prepend(0, ARRAY[1, 2])       AS prepended,    -- 插头
  array_cat(ARRAY[1, 2], ARRAY[3, 4]) AS concatenated  -- 拼接
LIMIT 1;

unnest 在 SELECT 列表里会把一行变成多行，因此上面的查询实际返回 3 行，LIMIT 1 只取第一行：

text
 built | unnested | appended | prepended | concatenated
-------+----------+----------+-----------+--------------
 {1,2,3} |        1 | {1,2,3}  | {0,1,2}   | {1,2,3,4}
(1 row)

语法与用法 ​

构造类函数 ​

函数 / 语法说明示例结果
ARRAY[e1, e2, ...]用表达式列表构造ARRAY[1+1, 2*3]{2,6}
ARRAY(SELECT ...)用子查询结果构造ARRAY(SELECT id FROM t)折叠成一个数组
array_agg(x)聚合函数，折叠多行array_agg(name ORDER BY id)保序数组
array_fill(v, dims)用同一值填充到指定维度array_fill(0, ARRAY[3]){0,0,0}

展开类函数 ​

函数说明典型位置
unnest(arr)把数组展开成多行SELECT ... FROM unnest(tags) AS tag
unnest(arr1, arr2)多数组按位置配对展开FROM unnest(a, b) AS t(x, y)
generate_subscripts(arr, dim)生成合法下标序列需要下标时用它而不是 generate_series

查询类函数 ​

函数 / 运算符说明示例结果
array_position(arr, v)首次出现的下标array_position(ARRAY['a','b','a'], 'a')1
array_position(arr, v, start)从 start 开始找array_position(ARRAY['a','b','a'], 'a', 2)3
array_positions(arr, v)所有出现的下标array_positions(ARRAY['a','b','a'], 'a'){1,3}
arr @> arr2arr 是否包含 arr2ARRAY[1,2,3] @> ARRAY[2]true
arr <@ arr2arr 是否被 arr2 包含ARRAY[2] <@ ARRAY[1,2,3]true
arr && arr2是否至少共享一个元素ARRAY[1,2] && ARRAY[2,3]true
v = ANY(arr)元素是否等于 v（等价于包含）2 = ANY(ARRAY[1,2,3])true
cardinality(arr)元素总数cardinality(ARRAY[1,2,3])3

@> / <@ / && 与 = ANY(...) 在单元素判断上等价，但语义侧重不同：

sql
-- 下面三种写法对"tags 里有没有 tech"等价
SELECT
  ('{tech,online}'::text[] @> ARRAY['tech'])      AS via_contains,
  ('{tech,online}'::text[] && ARRAY['tech'])      AS via_overlap,
  ('tech' = ANY('{tech,online}'::text[]))         AS via_any;
text
 via_contains | via_overlap | via_any
--------------+-------------+---------
 t            | t           | t
(1 row)

三者的关键差别在于索引与多值语义：@> 与 && 能走 GIN 索引；= ANY(...) 是逐行扫描中的表达式，通常用不上 GIN。多值场景下，@> 表示"全部包含"，&& 表示"包含任意一个"，别混用：

sql
-- 要求三个标签全部命中：@>
SELECT '{tech,online,launch}'::text[] @> ARRAY['tech','online']  AS all_hit;   -- true

-- 只要求命中任意一个：&&
SELECT '{tech,online,launch}'::text[] && ARRAY['tech','sales']   AS any_hit;   -- true
text
 all_hit | any_hit
---------+---------
 t       | t
(1 row)

变换类函数 ​

函数说明示例结果
array_append(arr, v)尾部追加一个元素array_append(ARRAY[1,2], 3){1,2,3}
array_prepend(v, arr)头部插入一个元素array_prepend(0, ARRAY[1,2]){0,1,2}
array_cat(a, b)拼接两个数组array_cat(ARRAY[1], ARRAY[2]){1,2}
array_remove(arr, v)删除所有等于 v 的元素array_remove(ARRAY[1,2,1], 1){2}
array_replace(a, old, new)替换所有等于 old 的元素array_replace(ARRAY[1,2], 2, 9){1,9}
array_to_string(arr, sep)用分隔符拼接成文本array_to_string(ARRAY[1,2], '-')1-2
string_to_array(s, sep)按分隔符拆成数组string_to_array('a,b', ','){a,b}
array_shrink(arr, n)只保留前 n 个元素（17+）array_shrink(ARRAY[1,2,3], 2){1,2}

注意 array_to_string 会跳过 NULL 元素（不产生分隔符），这是与 string_agg 行为一致的地方：

sql
-- NULL 元素被跳过，不会变成 "1--3"
SELECT array_to_string(ARRAY[1, NULL, 3], '-') AS joined;
text
 joined
--------
 1-3
(1 row)

详细说明 ​

ARRAY 构造器与 ARRAY(SELECT ...) 的区别 ​

两者外观相似，但求值方式完全不同：

维度ARRAY[e1, e2]ARRAY(SELECT ...)
元素来源当前行的表达式子查询的每一行
行数影响不改变行数不改变行数（折叠为单值）
空结果不适用产出 {}，不是 NULL
排序按书写顺序需显式 ORDER BY
等价聚合—array_agg(x ORDER BY ...)

ARRAY(SELECT ...) 与 array_agg 的等价关系值得记住，因为 ARRAY(SELECT ...) 更简洁且没有 GROUP BY 的负担：

sql
-- 两种写法，结果一致
SELECT ARRAY(SELECT name FROM t ORDER BY id) AS via_subquery;

SELECT array_agg(name ORDER BY id) AS via_agg FROM t;
text
 via_subquery
--------------
 {a,b,c}
(1 row)

但有一个重要差异：ARRAY(SELECT ...) 在结果包含 NULL 时会保留 NULL 元素，而它的行为在 PostgreSQL 中与 array_agg 一致——都保留 NULL。若要去掉，需显式过滤：

sql
-- 子查询里过滤掉 NULL
SELECT ARRAY(SELECT nickname FROM users WHERE nickname IS NOT NULL ORDER BY id) AS nicks;

unnest 的展开语义与 ROWS FROM ​

unnest 是最常被误用的数组函数。它出现在 FROM 中时，是把一个数组值"竖起来"成多行：

sql
SELECT tag FROM unnest(ARRAY['a', 'b', 'c']) AS tag;
text
 tag
-----
 a
 b
 c
(3 rows)

而出现在 SELECT 列表中时，它仍然会改变输出的行数，这是很多人没意识到的：

sql
-- 单行输入，三行输出
SELECT id, unnest(tags) AS tag FROM articles;
text
 id | tag
----+-------
  1 | tech
  1 | online
  2 | edu
(3 rows)

多数组并列展开时，PostgreSQL 采用 ROWS FROM 语义，按位置配对，短的一方补 NULL：

sql
SELECT * FROM unnest(ARRAY['a','b','c'], ARRAY[1,2]) AS t(letter, num);
text
 letter | num
--------+-----
 a      |   1
 b      |   2
 c      |
(3 rows)

要保留"元素在原数组中的下标"，用 WITH ORDINALITY，它会额外产出一列从 1 开始的行号——注意这是展开后的行号，与数组下标在标准情况下一致：

sql
SELECT tag, ord
FROM unnest(ARRAY['tech', 'online', 'launch']) WITH ORDINALITY AS t(tag, ord);
text
  tag   | ord
--------+-----
 tech   |   1
 online |   2
 launch |   3
(3 rows)

如果数组是从切片产生的（下标不从 1 开始），行号仍从 1 开始，此时想拿真实下标应使用 generate_subscripts：

sql
SELECT i, arr[i]
FROM (SELECT ('{a,b,c,d,e}'::text[])[2:4] AS arr) s,
     generate_subscripts(('{a,b,c,d,e}'::text[])[2:4], 1) AS i;
text
 i | arr
---+-----
 2 | b
 3 | c
 4 | d
(3 rows)

array_agg 与 string_agg 的取舍 ​

两者都是"把多行折叠成一个值"，区别只在输出形态：

维度array_agg(x)string_agg(x, sep)
返回类型数组text
是否需分隔符否必须
NULL 元素保留跳过
能否再展开能，unnest需 string_to_array 拆回
元素类型任意需能转 text
典型用途后续做集合运算直接展示给用户
sql
-- 同一组数据，两种落法
SELECT
  array_agg(name ORDER BY id)          AS as_array,
  string_agg(name, ', ' ORDER BY id)   AS as_text
FROM (VALUES ('甲', 1), ('乙', 2), ('丙', 3)) AS t(name, id);
text
      as_array      |  as_text
--------------------+------------
 {甲,乙,丙}         | 甲, 乙, 丙
(1 row)

选择规则：如果结果还要在 SQL 里继续做集合判断（比如再与另一个数组比 @>），用 array_agg；如果结果只用于展示或写日志，用 string_agg 更省事。把 array_agg 的结果直接 array_to_string 是常见的折中写法，但要记得它跳过 NULL。

array_agg 的去重、排序与 NULL 处理 ​

array_agg 支持 DISTINCT 与 ORDER BY，但两者组合时必须把排序键写进 ORDER BY：

sql
-- 去重 + 保序
SELECT array_agg(DISTINCT tag ORDER BY tag) AS uniq_tags
FROM unnest(ARRAY['b', 'a', 'b', 'c']) AS tag;
text
 uniq_tags
-----------
 {a,b,c}
(1 row)

array_agg 会保留 NULL 元素；要去掉 NULL 需在聚合前过滤：

sql
SELECT
  array_agg(x)                          AS with_null,   -- {1,NULL,3}
  array_agg(x) FILTER (WHERE x IS NOT NULL) AS no_null, -- {1,3}
  array_remove(array_agg(x), NULL)      AS removed     -- {1,3}
FROM (VALUES (1), (NULL), (3)) AS t(x);
text
 with_null  | no_null | removed
------------+---------+---------
 {1,NULL,3} | {1,3}   | {1,3}
(1 row)

三种写法里，FILTER 最直观，array_remove(..., NULL) 最紧凑，但要注意 array_remove 对 NULL 的处理依赖元素的等值比较，对不支持 = 的类型可能不适用。

位置查找：array_position 与 array_positions ​

array_position 找不到元素时返回 NULL 而不是 0 或报错，这与多数语言不同：

sql
SELECT
  array_position(ARRAY['a','b','a'], 'a')     AS first_a,    -- 1
  array_position(ARRAY['a','b','a'], 'a', 2)  AS second_a,   -- 3
  array_position(ARRAY['a','b'], 'z')         AS not_found,  -- NULL
  array_positions(ARRAY['a','b','a'], 'a')    AS all_a;      -- {1,3}
text
 first_a | second_a | not_found | all_a
---------+----------+-----------+-------
       1 |        3 |           | {1,3}
(1 row)

array_position 返回 NULL 的坑：WHERE array_position(tags, 'x') > 0 对不存在的元素会因 NULL > 0 为 NULL 而过滤掉——看似正确，但如果用 <> 0 判断就会出错：

sql
-- 错误！元素不存在时 NULL <> 0 也是 NULL，仍被过滤
-- 但若写成 NOT (array_position(tags,'x') = 0) 则两个 NULL 分支都会放行
-- 推荐：直接用 @> 或 = ANY()，语义清晰
SELECT count(*) FROM articles WHERE tags @> ARRAY['x'];

集合运算符与 GIN 索引 ​

@>、<@、&& 是数组列的索引友好运算符，配合 GIN 索引能让"标签包含"类查询走索引：

sql
CREATE INDEX idx_articles_tags_gin ON articles USING gin (tags);

-- 能走 GIN
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM articles WHERE tags @> ARRAY['database'];

-- 也支持数组列对数组列
SELECT * FROM articles WHERE tags && ARRAY['database', 'tutorial'];
text
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on articles
   Recheck Cond: (tags @> '{database}'::text[])
   ->  Bitmap Index Scan on idx_articles_tags_gin
         Index Cond: (tags @> '{database}'::text[])
(4 rows)

GIN 的默认操作符类 array_ops 支持 &&、@>、<@、= 四种运算符。注意 = ANY(...) 与 array_position 不走 GIN，改写成 @> / && 是常见的优化手段：

sql
-- 优化前：无法使用 GIN
SELECT * FROM articles WHERE 'database' = ANY(tags);

-- 优化后：等价且可使用 GIN
SELECT * FROM articles WHERE tags @> ARRAY['database'];

数组与集合的转换范式 ​

很多"数组问题"本质上是"集合问题"，先把数组展开成行、用关系运算处理、再聚合回数组，往往比堆砌数组函数更清晰：

sql
-- 范式一：去重
SELECT ARRAY(SELECT DISTINCT v FROM unnest(ARRAY['b','a','b']) AS v ORDER BY v) AS deduped;
-- {a,b}

-- 范式二：交集（两个数组的共同元素）
SELECT ARRAY(
  SELECT v FROM unnest(ARRAY[1,2,3]) AS v
  INTERSECT
  SELECT v FROM unnest(ARRAY[2,3,4]) AS v
  ORDER BY v
) AS intersect_arr;
-- {2,3}

-- 范式三：差集（在 a 不在 b）
SELECT ARRAY(
  SELECT v FROM unnest(ARRAY[1,2,3]) AS v
  EXCEPT
  SELECT v FROM unnest(ARRAY[2]) AS v
  ORDER BY v
) AS except_arr;
-- {1,3}
text
 deduped | intersect_arr | except_arr
---------+---------------+------------
 {a,b}   | {2,3}         | {1,3}
(1 row)

数组与字符串的互转 ​

string_to_array 与 array_to_string 是互逆的一对，但有两个不对称点：

方向函数不对称点
字符串 → 数组string_to_array(s, sep)s 为 NULL 时结果为 NULL；空串分割结果依分隔符而定
数组 → 字符串array_to_string(arr, sep)跳过 NULL 元素；不支持 NULL 用替代符时需 array_to_string(arr, sep, null_str)
sql
SELECT
  string_to_array('a,b,c', ',')              AS split_comma,     -- {a,b,c}
  string_to_array('a|b|c', '|')              AS split_pipe,      -- {a,b,c}
  string_to_array('', ',')                   AS split_empty,     -- {}
  array_to_string(ARRAY['a','b','c'], '-')   AS join_dash,       -- a-b-c
  array_to_string(ARRAY['a',NULL,'c'], '-', '?') AS join_with_null; -- a-?-c
text
 split_comma | split_pipe | split_empty | join_dash | join_with_null
-------------+------------+-------------+-----------+----------------
 {a,b,c}     | {a,b,c}    | {}          | a-b-c     | a-?-c
(1 row)

第三个参数是 NULL 替代符（PostgreSQL 9.1+），这是 array_to_string 特有的能力，string_agg 没有对应功能。

注意 string_to_array('', ',') 返回 {}，而 string_to_array('a', '') 会按每个字符拆分——空分隔符是特例，容易埋坑：

sql
SELECT string_to_array('abc', '') AS per_char;   -- {a,b,c}

数组元素与 DISTINCT ON、GROUP BY 的配合 ​

数组列本身可以参与 GROUP BY（按整个数组分组）和 DISTINCT（按整个数组去重），这在"多标签组合去重"场景很方便：

sql
-- 按标签组合统计出现次数
SELECT tags, count(*) AS cnt
FROM articles
GROUP BY tags
ORDER BY cnt DESC;

但要小心：GROUP BY tags 比的是数组的完整内容与顺序，{a,b} 与 {b,a} 是两组。要按"集合"分组需先排序或去重：

sql
-- 归一化后再分组：先排序去重，把 {b,a} 变成 {a,b}
SELECT ARRAY(SELECT DISTINCT v FROM unnest(tags) AS v ORDER BY v) AS tag_set,
       count(*) AS cnt
FROM articles
GROUP BY 1
ORDER BY cnt DESC;

实战示例 ​

场景：为电商商品表设计 SKU 规格数组，用数组函数完成筛选、统计、聚合与索引优化。

第 1 步：建表与索引

sql
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku        TEXT   NOT NULL UNIQUE,
  name       TEXT   NOT NULL,
  tags       TEXT[] NOT NULL DEFAULT '{}',
  colors     TEXT[] NOT NULL DEFAULT '{}',
  prices     NUMERIC(10,2)[] NOT NULL DEFAULT '{}',
  CONSTRAINT chk_tags  CHECK (cardinality(tags) <= 10),
  CONSTRAINT chk_colors CHECK (cardinality(colors) <= 20)
);

-- 标签集合查询用 GIN
CREATE INDEX idx_products_tags_gin ON products USING gin (tags);
text
CREATE TABLE
CREATE INDEX

第 2 步：插入数据

sql
INSERT INTO products (sku, name, tags, colors, prices) VALUES
  ('SKU-1001', '速干运动T恤',  ARRAY['sport','summer'],   ARRAY['black','white','red'],   ARRAY[99.00, 109.00]),
  ('SKU-1002', '防水登山鞋',  ARRAY['sport','outdoor'],   ARRAY['brown','black'],         ARRAY[499.00, 529.00]),
  ('SKU-1003', '纯棉基础衬衫', ARRAY['office','casual'],   ARRAY['white','blue'],          ARRAY[159.00]),
  ('SKU-1004', '轻量羽绒服',  ARRAY['outdoor','winter'],  ARRAY['red','blue','green'],    ARRAY[799.00, 859.00, 899.00]),
  ('SKU-1005', '沙滩短裤',    ARRAY['summer','casual'],   ARRAY['blue'],                  ARRAY[69.00])
RETURNING sku, cardinality(tags) AS tag_cnt, cardinality(colors) AS color_cnt;
text
   sku    | tag_cnt | color_cnt
----------+---------+-----------
 SKU-1001 |       2 |         3
 SKU-1002 |       2 |         2
 SKU-1003 |       2 |         2
 SKU-1004 |       2 |         3
 SKU-1005 |       2 |         1
(5 rows)

第 3 步：集合包含与重叠查询

sql
-- 同时带 sport 和 outdoor 标签（全部命中）
SELECT sku, name, tags
FROM products
WHERE tags @> ARRAY['sport', 'outdoor'];

-- 带 sport 或 summer 任一标签（任意命中）
SELECT sku, name, tags
FROM products
WHERE tags && ARRAY['sport', 'summer']
ORDER BY sku;
text
   sku    |    name    |        tags
----------+------------+---------------------
 SKU-1002 | 防水登山鞋 | {sport,outdoor}
(1 row)

   sku    |     name     |        tags
----------+--------------+---------------------
 SKU-1001 | 速干运动T恤  | {sport,summer}
 SKU-1002 | 防水登山鞋   | {sport,outdoor}
 SKU-1003 | 纯棉基础衬衫 | {office,casual}
 SKU-1004 | 轻量羽绒服   | {outdoor,winter}
 SKU-1005 | 沙滩短裤     | {summer,casual}
(5 rows)

第 4 步：展开数组做标签统计

sql
SELECT tag, count(*) AS product_cnt
FROM products, unnest(tags) AS tag
GROUP BY tag
ORDER BY product_cnt DESC, tag;
text
   tag   | product_cnt
---------+-------------
 summer  |           2
 casual  |           2
 outdoor |           2
 sport   |           2
 office  |           1
 winter  |           1
(6 rows)

第 5 步：把颜色聚合成数组（跨行折叠）

sql
-- 找出每个标签下涉及的所有颜色（去重 + 排序）
SELECT
  tag,
  ARRAY(SELECT DISTINCT c FROM unnest(array_agg(colors)) AS c ORDER BY c) AS all_colors
FROM products, unnest(tags) AS tag
GROUP BY tag
ORDER BY tag;
text
   tag   |      all_colors
---------+-----------------------
 casual  | {blue,white}
 office  | {blue,white}
 outdoor | {black,blue,brown,green,red}
 sport   | {black,brown,red,white}
 summer  | {black,blue,red,white}
 winter  | {blue,green,red}
(6 rows)

这里 unnest(array_agg(colors)) 是"先折叠成二维数组、再拉平"的常用套路：array_agg 把多行数组聚成一个"数组的数组"，unnest 把它拉平成一维元素流。

sql
-- 验证中间结果：array_agg(colors) 产出的是一个数组的数组
SELECT array_agg(colors) AS nested_arrays
FROM products
WHERE tags @> ARRAY['sport'];
text
          nested_arrays
--------------------------------------
 {"{black,white,red}","{brown,black}"}
(1 row)

第 6 步：用 WITH ORDINALITY 保留下标

sql
-- 展开价格数组，同时给出"第几档价格"
SELECT p.sku, pr.ord AS price_tier, pr.value AS price
FROM products p
CROSS JOIN LATERAL unnest(p.prices) WITH ORDINALITY AS pr(value, ord)
WHERE p.sku = 'SKU-1004'
ORDER BY pr.ord;
text
   sku    | price_tier | price
----------+------------+--------
 SKU-1004 |          1 | 799.00
 SKU-1004 |          2 | 859.00
 SKU-1004 |          3 | 899.00
(3 rows)

第 7 步：清理

sql
DROP TABLE products;
text
DROP TABLE

注意事项 ​

  • unnest 在 SELECT 列表中会改变行数，不只是"展开一列"。若只想取值不想增行，应改用下标 arr[1] 或 cardinality。
sql
-- 一行变三行
SELECT id, unnest(tags) FROM articles;

-- 只取第一项，不增行
SELECT id, tags[1] AS first_tag FROM articles;
  • array_position 找不到时返回 NULL 而非 0，用 > 0 判断安全，用 = 0 判断会漏。
sql
-- 安全
SELECT * FROM articles WHERE array_position(tags, 'x') > 0;
-- 等价且更清晰（可走 GIN）
SELECT * FROM articles WHERE tags @> ARRAY['x'];
  • @> 是"全部包含"，&& 是"任意一个"，多值查询选错会返回错误结果集。
sql
-- 同时含 a 和 b
WHERE tags @> ARRAY['a', 'b']
-- 含 a 或 b
WHERE tags && ARRAY['a', 'b']
  • 数组的相等比较是"内容 + 顺序 + 维度"三重一致，{a,b} 与 {b,a} 不相等。
sql
SELECT '{a,b}'::text[] = '{b,a}'::text[] AS same_content;   -- false
text
 same_content
--------------
 f
(1 row)
  • array_agg 不保证顺序，需要确定顺序时必须写 ORDER BY，且 DISTINCT 与 ORDER BY 同时出现时排序表达式要在聚合参数中。
sql
-- 推荐
array_agg(tag ORDER BY position)
-- 不推荐（顺序随执行计划变化）
array_agg(tag)
  • array_agg 保留 NULL，string_agg 跳过 NULL，两者行为不同，展示场景要统一口径。
sql
SELECT array_agg(x) AS arr, string_agg(x::text, ',') AS str
FROM (VALUES ('a'), (NULL), ('c')) AS t(x);
text
   arr    |  str
----------+-------
 {a,NULL,c} | a,c
(1 row)
  • ARRAY(SELECT ...) 的子查询在整条 SQL 里是关联子查询时会按行执行，可能变成 N 次扫描，性能远差于 JOIN + array_agg。
sql
-- 危险：每行都执行一次子查询
SELECT p.id, ARRAY(SELECT c.name FROM colors c WHERE c.product_id = p.id) AS colors
FROM products p;

-- 推荐：一次连接 + 聚合
SELECT p.id, array_agg(c.name ORDER BY c.name) AS colors
FROM products p
LEFT JOIN colors c ON c.product_id = p.id
GROUP BY p.id;
  • array_to_string 跳过 NULL，不产生多余分隔符，若需占位要传第三个参数。
sql
SELECT array_to_string(ARRAY['a', NULL, 'c'], '-', '(空)') AS with_placeholder;
text
 with_placeholder
------------------
 a-(空)-c
(1 row)
  • GIN 索引只支持 &&、@>、<@、=，= ANY(...)、array_position 都不走索引，需要改写。
sql
-- 不走索引
WHERE 'x' = ANY(tags)
-- 走索引
WHERE tags @> ARRAY['x']
  • 数组元素写回是整行重写。array_append 在 UPDATE 中使用会重写整行并可能触发 TOAST，高频追加场景应改用关系表。
sql
-- 每次追加都会读改写整行
UPDATE products SET tags = array_append(tags, 'new') WHERE id = 1;
  • 多维数组无法直接 unnest 成一维，unnest 只展开最外层，内层仍是数组。
sql
-- 外层展开后每行仍是一个数组
SELECT unnest(ARRAY[[1,2],[3,4]]) AS row_arr;
text
 row_arr
---------
 {1,2}
 {3,4}
(2 rows)
  • 空数组不等于 NULL，cardinality('{}') = 0 而 cardinality(NULL) = NULL，判空要统一口径。
sql
SELECT
  cardinality('{}'::int[])   AS empty_card,   -- 0
  cardinality(NULL::int[])   AS null_card;    -- NULL
text
 empty_card | null_card
------------+-----------
          0 |
(1 row)

最佳实践 ​

  • 能用集合运算符就别用逐元素函数。tags @> ARRAY['x'] 比 array_position(tags,'x') > 0 更快、更清晰、可走 GIN。
sql
-- 推荐
SELECT * FROM products WHERE tags @> ARRAY['sport'];
  • "展开—处理—聚合"是数组的通用范式，遇到复杂的数组变换（去重、交并差、排序），先 unnest 成行再聚合回来。
sql
SELECT ARRAY(SELECT DISTINCT v FROM unnest(tags) AS v ORDER BY v) FROM products;
  • 给数组列建 GIN 索引，但只在真的有集合查询时才建，因为 GIN 有写放大代价。
sql
CREATE INDEX idx_products_tags_gin ON products USING gin (tags);
  • 数组列统一 NOT NULL DEFAULT '{}'，避免 NULL 与空数组两种"没有值"的表示并存。
sql
tags TEXT[] NOT NULL DEFAULT '{}'
  • 用 cardinality 做数量判断与约束，array_length 对空数组返回 NULL 会让 CHECK 失效。
sql
CONSTRAINT chk_tags CHECK (cardinality(tags) <= 10)
  • array_agg 一律显式写 ORDER BY，顺序稳定才能被测试与缓存依赖。
sql
array_agg(tag ORDER BY position)
  • 需要下标时用 WITH ORDINALITY，而不是先 generate_series 再数组取值，前者只扫一次。
sql
CROSS JOIN LATERAL unnest(p.prices) WITH ORDINALITY AS pr(value, ord)
  • 展示给用户前转成文本，不要直接把 {a,b,c} 这种内部格式丢到界面。
sql
SELECT array_to_string(tags, ' / ') AS tag_text FROM products;
  • ARRAY(SELECT ...) 只用于非关联的简单折叠，任何带 WHERE 外列 的子查询都应改写成 JOIN + array_agg。
sql
-- 推荐
SELECT p.id, array_agg(c.name ORDER BY c.name)
FROM products p LEFT JOIN colors c ON c.product_id = p.id
GROUP BY p.id;
  • 迁移数组到关系表前先看查询模式。一旦需要"按标签分页、按标签建外键、按标签做多表联查"，数组会迅速成为负担。

下一节

继续学习：JSONB 构建函数（jsonb_build_object / jsonb_agg）

参考链接 ​