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。