数组操作符

数组的很多能力并不来自函数，而是来自操作符（Operator）。比较、包含、重叠、连接这些判断都可以用简洁的符号表达，而且其中一部分还能直接走 GIN 索引。本节把数组操作符逐个讲透，并说明它们与索引、ANY / ALL 之间的关系。

前置知识

阅读本节前，建议先了解：数组访问与修改

基础概念

数组操作符（Array Operator）

数组操作符是 PostgreSQL 为数组类型专门重载的一批符号。它们看起来像通用的比较符号，但语义是"按数组整体"或"按集合"来理解的。

操作符	名称	返回	语义
`=`	相等	`boolean`	两个数组长度、维度、逐元素值全部相同
`<>`	不等	`boolean`	与 `=` 相反
`<` `<=` `>` `>=`	排序比较	`boolean`	逐元素字典序比较，通常不常用
`@>`	包含（Contains）	`boolean`	左边包含右边（右为左的子集）
`<@`	被包含（Contained By）	`boolean`	左边是右边的子集
`&&`	重叠（Overlap）	`boolean`	两个数组至少有一个共同元素
`\|\|`	连接（Concatenate）	数组	拼接两个数组（或数组与元素）

包含（Containment）与重叠（Overlap）

这两个概念是数组查询的核心：

包含：A @> B 表示 B 中的每个元素都能在 A 中找到。注意方向，反直觉的是"大集合在左边"。
重叠：A && B 表示存在至少一个元素同时属于两者。集合论中的"交集非空"。

用集合符号写出来更清楚：

text

A @> B   等价于   B ⊆ A
A <@ B   等价于   A ⊆ B
A && B   等价于   A ∩ B ≠ ∅

集合（Set）视角

在处理数组时，把数组当成"允许重复元素的集合"来思考会更自然。一个重要推论是：重复元素不影响 @> 与 && 的结果。

sql

-- 重复元素不影响包含判断
SELECT ARRAY[1, 2, 2, 3] @> ARRAY[2, 3] AS contains;   -- true
SELECT ARRAY[1, 2, 2, 3] @> ARRAY[2, 2]   AS contains;   -- true
SELECT ARRAY[1, 2, 2, 3] && ARRAY[3, 3, 5] AS overlaps;  -- true

但重复元素会影响 =，因为 = 是逐元素精确比较：

sql

SELECT ARRAY[1, 2, 2, 3] = ARRAY[1, 2, 3] AS is_equal;   -- false

术语提示

数组（Array）、包含（Containment）、重叠（Overlap）、子集（Subset）、连接（Concatenation）。本节多次用到这些术语。

语法与用法

数组操作符的语法骨架如下：

text

-- 包含 / 被包含 / 重叠
array_expr  @>  array_expr
array_expr  <@  array_expr
array_expr  &&  array_expr

-- 连接
array_expr  ||  array_expr
element     ||  array_expr
array_expr  ||  element

-- 相等比较
array_expr  =   array_expr

一条典型的"标签筛选"查询：

sql

-- 找出同时包含 'postgresql' 和 'sql' 两个标签的文章
SELECT id, title, tags
FROM articles
WHERE tags @> ARRAY['postgresql', 'sql'];

-- 找出包含任一标签的文章
SELECT id, title, tags
FROM articles
WHERE tags && ARRAY['postgresql', 'mysql'];

准备本节示例数据：

sql

-- 若已存在则重建，保证示例可重复执行
DROP TABLE IF EXISTS articles;

CREATE TABLE articles (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title  TEXT   NOT NULL,
  tags   TEXT[] NOT NULL DEFAULT '{}',   -- 标签数组
  authors TEXT[] NOT NULL DEFAULT '{}'   -- 作者数组
);

INSERT INTO articles (title, tags, authors) VALUES
  ('PostgreSQL 数组入门',  ARRAY['postgresql','sql','array'],  ARRAY['alice']),
  ('PostgreSQL 索引原理',  ARRAY['postgresql','index'],        ARRAY['alice','bob']),
  ('MySQL 索引原理',       ARRAY['mysql','index'],             ARRAY['bob']),
  ('SQL 通用查询技巧',     ARRAY['sql','query'],               ARRAY['carol','bob']),
  ('无标签草稿',           ARRAY[]::TEXT[],                    ARRAY['carol']);

详细说明

`@>` 包含

@> 是最常用的数组操作符。判断"右边的数组是不是左边的子集"。

sql

-- 同时含有 postgresql 与 index
SELECT title
FROM articles
WHERE tags @> ARRAY['postgresql','index'];

-- 结果：
--       title
-- --------------------
--  PostgreSQL 索引原理

空数组的边界行为：任何数组都包含空数组，空数组只包含空数组。

sql

SELECT tags @> '{}' AS contains_empty FROM articles WHERE id = 5;   -- true
SELECT '{}'::TEXT[] @> '{}'  AS empty_contains_empty;               -- true
SELECT '{}'::TEXT[] @> ARRAY['a'] AS empty_contains_a;              -- false

NULL 元素：@> 把 NULL 当成一个普通值来比较，所以 ARRAY[1,NULL] @> ARRAY[NULL] 为 true。

sql

SELECT ARRAY[1, NULL] @> ARRAY[NULL] AS c1;   -- true
SELECT ARRAY[1, NULL] = ARRAY[1, NULL] AS c2; -- true
SELECT ARRAY[1] = ARRAY[1, NULL]      AS c3;  -- false

`<@` 被包含

<@ 是 @> 的反向形式，语义完全对称：

sql

SELECT ARRAY['postgresql'] <@ ARRAY['postgresql','index'] AS r1;   -- true
SELECT ARRAY['postgresql','index'] <@ ARRAY['postgresql'] AS r2;   -- false

等价改写：

sql

-- 下面两条查询等价
SELECT 1 WHERE ARRAY[1,2] @> ARRAY[1];
SELECT 1 WHERE ARRAY[1] <@ ARRAY[1,2];

实际业务中 <@ 常用于"这个数组的元素都在允许集合内"：

sql

-- 校验：标签必须全部属于白名单
SELECT id, title
FROM articles
WHERE NOT (tags <@ ARRAY['postgresql','sql','mysql','index','array','query']);

`&&` 重叠

&& 判断两个数组是否有公共元素，只要有一个就返回 true。

sql

-- 含有任一指定标签的文章
SELECT title, tags
FROM articles
WHERE tags && ARRAY['mysql','query']
ORDER BY id;

-- 结果：
--        title       |         tags
-- -------------------+-----------------------
--  MySQL 索引原理     | {mysql,index}
--  SQL 通用查询技巧   | {sql,query}

与 @> 的对比非常关键：

sql

-- @> 要求"全部包含"，条件更严格
SELECT count(*) FROM articles WHERE tags @> ARRAY['mysql','index'];  -- 1
-- && 只要求"有交集"，条件更宽松
SELECT count(*) FROM articles WHERE tags && ARRAY['mysql','index'];  -- 2

第二个查询会多出 SQL 通用查询技巧（含 query 但与 index 无交集？不，mysql 也没有），实际多出的是同时含 index 或 mysql 之一的行——本例中 SQL 通用查询技巧 两者都没有，因此需注意：

sql

-- 精确核对哪两行命中
SELECT title, tags
FROM articles
WHERE tags && ARRAY['mysql','index']
ORDER BY id;

-- 结果：
--       title       |      tags
-- ------------------+------------------
--  PostgreSQL 索引原理 | {postgresql,index}
--  MySQL 索引原理      | {mysql,index}

提示

&& 与 @> 都支持 GIN 索引，但不支持 B-tree 索引。选择哪个取决于业务语义是"全包含"还是"任一命中"。

`||` 连接

|| 在数组上下文中有多重身份，同一个符号被重载了多次：

左侧类型	右侧类型	结果
数组	数组	数组（拼接）
元素	数组	数组（前插单元素）
数组	元素	数组（追加单元素）

sql

SELECT ARRAY[1,2] || ARRAY[3,4]     AS a1;   -- {1,2,3,4}
SELECT 0 || ARRAY[1,2]             AS a2;   -- {0,1,2}
SELECT ARRAY[1,2] || 3             AS a3;   -- {1,2,3}
SELECT ARRAY['a'] || ARRAY[]::TEXT[] AS a4; -- {a}

重要陷阱：|| 与文本字符串的歧义。当左右都是 text 类型时，|| 会被解析为字符串拼接而不是数组拼接。

sql

-- 字符串拼接，结果是 '12'，不是数组
SELECT '1' || '2' AS s;              -- '12'

-- 想拼接数组必须显式转换
SELECT ARRAY['1'] || ARRAY['2'] AS arr;   -- {1,2}
SELECT ('{1}'::TEXT[]) || ARRAY['2'] AS arr2;  -- {1,2}

多维数组拼接：|| 对多维数组只沿第一维拼接，但要求其他维度一致。

sql

SELECT ARRAY[[1,2],[3,4]] || ARRAY[[5,6]] AS m;
-- 结果：{{1,2},{3,4},{5,6}}

`=` 相等比较

数组相等要求长度、维度、每个位置的元素都相同，是严格的逐元素比较。

sql

SELECT ARRAY[1,2,3] = ARRAY[1,2,3] AS r1;   -- true
SELECT ARRAY[1,2,3] = ARRAY[3,2,1] AS r2;   -- false（顺序不同）
SELECT ARRAY[1,2,3] = ARRAY[1,2]   AS r3;   -- false（长度不同）
SELECT ARRAY[1,2,3] = ARRAY[1,2,3,4] AS r4; -- false

下界差异也影响相等？不会。PostgreSQL 数组相等只看元素内容，非 1 起点的数组会被视为与起点 1 的数组相等吗？实际上会被视为不同：

sql

-- 切片得到的数组下界可能不是 1
SELECT (ARRAY[10,20,30,40])[2:3] AS slice;   -- {20,30}
SELECT (ARRAY[10,20,30,40])[2:3] = ARRAY[20,30] AS eq;  -- true

实际上 PostgreSQL 14 起数组相等比较不比较下界信息（lb），两个内容相同的数组即使下界不同也判为相等。为避免歧义，建议比较前用 array_dims 确认形状。

`ANY` / `ALL` 与数组配合

ANY 与 ALL 是数组最常见的"逐元素"用法，它们不是数组操作符，但语义上比操作符更直观。

text

element = ANY (array)    -- 数组中存在等于该元素的值，等价于 array @> ARRAY[element]
element = ALL (array)    -- 数组中所有元素都等于该值

sql

SELECT 'index' = ANY (tags) AS has_index FROM articles WHERE id = 2;   -- true
SELECT 5 > ALL (ARRAY[1,2,3]) AS r1;   -- true
SELECT 2 > ANY (ARRAY[1,2,3]) AS r2;   -- true
SELECT 2 >= ALL (ARRAY[1,2,3]) AS r3;  -- false

= ANY 与 @> 的等价与差异：

sql

-- 下面两条结果相同
SELECT id FROM articles WHERE 'index' = ANY (tags);
SELECT id FROM articles WHERE tags @> ARRAY['index'];

但 = ANY 在不指定操作符类的情况下不走 GIN 索引（优化器需要改写），而 @> 可以直接匹配 GIN 的 array_ops 操作符类。所以做索引优化时优先写 @>。

ANY 配合比较操作符可以表达"存在满足条件的元素"：

sql

-- 是否存在长度超过 6 的标签
SELECT title, tags
FROM articles
WHERE EXISTS (
  SELECT 1 FROM unnest(tags) AS t(tag) WHERE length(tag) > 6
);

-- 等价写法（用比较操作符 + ANY）
SELECT title
FROM articles
WHERE length(tags::text) > 0;   -- 只是演示，非真正等价

操作符与 GIN 索引的关系

数组的 @>、<@、&&、= 都由默认的 GIN 操作符类 array_ops 支持。建立索引后，这些操作符可以从顺序扫描升级为索引扫描。

sql

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 走索引：tags @> ARRAY['postgresql']
SELECT id FROM articles WHERE tags @> ARRAY['postgresql'];

-- 走索引：tags && ARRAY['mysql','query']
SELECT id FROM articles WHERE tags && ARRAY['mysql','query'];

不支持索引的操作符：

操作符	GIN `array_ops` 支持	说明
`@>`	是	最常用的索引查询
`<@`	是	与 `@>` 对称
`&&`	是	重叠查询
`=`	是	精确相等
`<` `<=` `>` `>=`	否	排序比较无法用 GIN
`\|\|`	否	表达式，不产生查询条件

详细的索引原理与 EXPLAIN 验证见数组索引与性能。

常用组合实例

把操作符组合起来，可以表达相当复杂的筛选逻辑。

sql

-- 组合 1：必须含 postgresql，且不含某个标签
SELECT title
FROM articles
WHERE tags @> ARRAY['postgresql']
  AND NOT (tags && ARRAY['draft']);

-- 组合 2：至少两个指定标签都命中（用 @> 表达"全部"）
SELECT title
FROM articles
WHERE tags @> ARRAY['postgresql','index'];

-- 组合 3：任意作者命中
SELECT title, authors
FROM articles
WHERE authors && ARRAY['alice','carol']
ORDER BY id;

实战示例

下面用一个"文章标签筛选"的完整流程，串起本节所有操作符。

第 1 步：准备数据

sql

DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title    TEXT   NOT NULL,
  tags     TEXT[] NOT NULL DEFAULT '{}',
  status   TEXT   NOT NULL DEFAULT 'published'
);

INSERT INTO posts (title, tags, status) VALUES
  ('PG 数组操作符详解', ARRAY['postgresql','array','operator'], 'published'),
  ('PG GIN 索引实战',   ARRAY['postgresql','index','gin'],      'published'),
  ('PG 窗口函数',       ARRAY['postgresql','window'],           'published'),
  ('待审草稿',          ARRAY['postgresql','draft'],            'draft'),
  ('Redis 入门',        ARRAY['redis','nosql'],                 'published');

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

第 2 步：包含与重叠查询

sql

-- 全部包含
SELECT title
FROM posts
WHERE tags @> ARRAY['postgresql','index'];

-- 结果：
--       title
-- ------------------
--  PG GIN 索引实战

-- 任一重叠
SELECT title, tags
FROM posts
WHERE tags && ARRAY['index','window']
ORDER BY id;

-- 结果：
--       title       |        tags
-- ------------------+---------------------
--  PG GIN 索引实战   | {postgresql,index,gin}
--  PG 窗口函数       | {postgresql,window}

第 3 步：排除某类内容

sql

-- 已发布且不含 draft 的文章
SELECT title
FROM posts
WHERE status = 'published'
  AND NOT (tags && ARRAY['draft'])
ORDER BY id;

-- 结果：
--        title
-- --------------------
--  PG 数组操作符详解
--  PG GIN 索引实战
--  PG 窗口函数
--  Redis 入门

第 4 步：用 `||` 追加标签

sql

-- 给某篇文章追加一个新标签
UPDATE posts
SET tags = tags || 'featured'
WHERE id = 1
RETURNING title, tags;

-- 结果：
--        title        |              tags
-- --------------------+---------------------------------
--  PG 数组操作符详解  | {postgresql,array,operator,featured}

第 5 步：批量合并标签

sql

-- 把两篇文章的标签做并集（去重）
SELECT ARRAY(
  SELECT DISTINCT unnest(tags)
  FROM posts
  WHERE id IN (1, 2)
) AS merged_tags;

-- 结果：
--           merged_tags
-- --------------------------------
--  {postgresql,array,operator,index,gin}

第 6 步：统计标签出现次数

sql

-- 展开全部标签并统计词频
SELECT tag, count(*) AS cnt
FROM posts, unnest(tags) AS tag
GROUP BY tag
ORDER BY cnt DESC, tag;

-- 结果：
--     tag      | cnt
-- -------------+-----
--  postgresql | 4
--  array      | 1
--  draft      | 1
--  featured   | 1
--  gin        | 1
--  index      | 1
--  nosql      | 1
--  operator   | 1
--  redis      | 1
--  window     | 1

第 7 步：清理

sql

DROP TABLE posts;

注意事项

@> 与 <@ 方向容易记反：记住"大集合在左"——A @> B 是 B ⊆ A。写查询时可以先用小数据集验证。
重复元素不影响 @> / &&，但影响 =：ARRAY[1,1] @> ARRAY[1] 为 true，但 ARRAY[1,1] = ARRAY[1] 为 false。
NULL 是普通元素：ARRAY[1,NULL] @> ARRAY[NULL] 返回 true。如果业务上把 NULL 当作"未知"，需要显式过滤。
|| 存在类型歧义：左右都是 text 时按字符串拼接解析，会得到 ERROR: operator does not exist: text[] || text 或错误结果。务必用 ARRAY[...] 或显式类型转换。
&& 比 @> 宽泛：&& 只要求交集非空，容易命中意外数据。业务上要"全部满足"时用 @>。
排序比较操作符不适合业务使用：< <= > >= 的字典序比较规则不直观，且不走索引。
= ANY 默认不走 GIN 索引：需要索引加速时改写为 @>。
空数组的语义：'{}' 是长度为 0 的数组，不是 NULL；任何数组都包含空数组，这会让 tags @> '{}' 恒为 true。
数组元素类型必须一致：ARRAY[1, 'a'] 会升级为 text[]，混合类型的比较可能不符合预期。

最佳实践

筛选语义优先选操作符：需要"全部包含"用 @>，需要"任一命中"用 &&，需要"全部属于"用 <@。
为高频筛选列建 GIN 索引：CREATE INDEX ... USING GIN (col) 是数组列的标准做法。
避免在数组元素上做范围比较：如果需要按元素范围筛选，考虑拆成关系表或用 JSONB。
用 ANY 做可读性表达，用操作符做性能表达：代码可读性优先时用 = ANY，索引敏感路径改写为 @>。
追加元素统一用 || 或 array_append：不要用 UPDATE ... SET tags[array_length(tags,1)+1] = x，边界容易出错。
合并标签时显式去重：ARRAY(SELECT DISTINCT unnest(...)) 是安全的并集写法。
测试空数组与 NULL 边界：tags @> '{}'、tags && '{}' 的返回值要写进测试用例。
注意操作符优先级：NOT 作用范围、|| 与比较操作符混用时用括号明确意图。

继续学习：数组函数

参考链接

PostgreSQL 官方文档：Arrays
PostgreSQL 官方文档：Array Functions and Operators
PostgreSQL 官方文档：Index Types
PostgreSQL 官方文档：GIN Indexes

数组操作符 ​

基础概念 ​

数组操作符（Array Operator） ​

包含（Containment）与重叠（Overlap） ​

集合（Set）视角 ​

语法与用法 ​

详细说明 ​

@> 包含 ​

<@ 被包含 ​

&& 重叠 ​

|| 连接 ​

= 相等比较 ​

ANY / ALL 与数组配合 ​

操作符与 GIN 索引的关系 ​

常用组合实例 ​

实战示例 ​

第 1 步：准备数据 ​

第 2 步：包含与重叠查询 ​

第 3 步：排除某类内容 ​

第 4 步：用 || 追加标签 ​

第 5 步：批量合并标签 ​

第 6 步：统计标签出现次数 ​

第 7 步：清理 ​

数组操作符

基础概念

数组操作符（Array Operator）

包含（Containment）与重叠（Overlap）

集合（Set）视角

语法与用法

详细说明

`@>` 包含

`<@` 被包含

`&&` 重叠

`||` 连接

`=` 相等比较

`ANY` / `ALL` 与数组配合

操作符与 GIN 索引的关系

常用组合实例

实战示例

第 1 步：准备数据

第 2 步：包含与重叠查询

第 3 步：排除某类内容

第 4 步：用 `||` 追加标签

第 5 步：批量合并标签

第 6 步：统计标签出现次数

第 7 步：清理