Menu

PostgreSQL 数组:ANY、包含(@>)与 unnest

PostgreSQL 数组列:如何声明,下标从 1 开始,用 ANY、@> 和 && 判断数组是否包含某个值,unnest,以及常用的数组函数。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

PostgreSQL 的任何类型都可以做成数组。把列声明为 text[] 或 int[],值写成 '{a,b}' 或 ARRAY['a', 'b'],用 tags[1] 读取元素(下标从 1 开始)。要判断数组是否包含某个值,用 'value' = ANY(tags) 或 tags @> ARRAY['value']。

文章 1 和 2 带有标签 sql。tags[1] 是第一个元素,cardinality 统计元素个数。两种字面量写法存储的值相同;当元素来自表达式或者包含逗号时,ARRAY[...] 更方便。

判断数组是否包含某个值

判断成员关系有四个操作符:

判断含义对 '{sql,json}' 是否为真
'sql' = ANY(tags)某个值在数组中是
tags @> '{sql,json}'数组包含所有这些值是
tags <@ '{sql,json,go}'每个元素都在这个列表中是
tags && '{go,json}'两个数组至少有一个相同元素是

只有文章 1 同时带两个标签。文章 2 和 3 与 {go,json} 有共同元素。文章 3 是唯一一个 no_go 为假的。ALL 是 ANY 的对应写法:比较必须对每个元素都成立。

= ANY 遵循 SQL 的 NULL 规则。如果没有找到这个值,而数组里有 NULL,结果是 NULL,而不是假。所以 WHERE NOT ('x' = ANY(tags)) 可能会漏掉你以为会返回的行。@> 和 && 只返回真或假。

在大表上,GIN 索引能让 @>、<@、&& 和 = 变快。它对 = ANY(column) 没有帮助,所以需要走索引的查找要写成 tags @> ARRAY['sql']:

CREATE INDEX posts_tags_idx ON posts USING gin (tags);

用 unnest 把数组展开成行

unnest 为每个元素返回一行,这样你就可以对单个元素计数、连接或筛选:

sql 出现在两篇文章中,其他标签各出现一次。加上 WITH ORDINALITY(unnest(tags) WITH ORDINALITY AS t(tag, pos))还能得到每个元素的位置。要把行重新收集成数组,用 array_agg(见 string_agg 和 array_agg)。

添加、删除和修改元素

结果依次是 {1,2,3}、{1,2,3,4}、{2,3}、{1,9,3}、2、{20,30} 和 a, b, c。在 UPDATE 中,同样的函数可以修改存储的数组:SET tags = array_append(tags, 'new') 或 SET tags = array_remove(tags, 'old')。array_remove 会删除这个值的所有副本。string_to_array('a,b,c', ',') 把文本拆成数组。

获取数组长度

cardinality(tags) 返回元素个数,空数组返回 0。array_length(tags, 1) 返回第 1 维的长度,空数组返回 NULL。所以 WHERE array_length(tags, 1) = 0 永远不会匹配。判断空数组请用 cardinality(tags) = 0 或 tags = '{}'。

PostgreSQL 数组可以有多个维度(int[][]),但每个子数组的长度必须相同,而且声明的大小(int[3])并不会被强制执行。对于嵌套或长短不一的数据,JSONB 通常更合适。

常见问题

PostgreSQL 数组的下标从 0 还是从 1 开始?

默认从 1 开始:tags[1] 是第一个元素。超出末尾的下标返回 NULL,不会报错。

PostgreSQL 中 IN 和 = ANY 有什么区别?

x IN (1, 2, 3) 接收写在查询里的列表;x = ANY(array) 接收一个数组值。ANY 更适合应用代码,因为你可以只传一个数组参数(WHERE id = ANY($1)),不必拼出一串占位符。

应该用数组列还是单独建一张表?

数组适合和所在行一起读写的短列表,比如标签。如果每一项需要自己的列、外键,或者要逐个更新,那么每项一行的单独表更容易查询,也更容易加约束。

PostgreSQL 中怎么判断数组是否为空?

用 cardinality(arr) = 0 或 arr = '{}'。array_length(arr, 1) = 0 永远不会匹配,因为空数组的 array_length 返回 NULL。

Coddy 编程语言插图

和 Coddy 一起学编程

开始