PostgreSQL / 系统课时
数组类型
数组类型
当一条记录需要保存少量同类型值,例如标签、候选端口或权限代码时,PostgreSQL 数组可以减少简单关联表。本课将创建文本数组字段,完成成员查询,并说明数组建模的适用边界。
学习目标
- 创建、写入和读取 PostgreSQL 数组字段
- 使用数组运算符筛选包含指定元素的记录
- 判断数组与关联表各自适合的场景
创建数组字段
数组类型在元素类型后添加 [],例如 text[]、integer[]。同一数组中的元素必须能转换为声明的基础类型,数组本身仍可设置 NOT NULL 和默认值。
下面创建文章表,保存多个标签,并建立 GIN 索引支持包含查询。
sql
CREATE TEMP TABLE articles (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
tags text[] NOT NULL DEFAULT '{}'
);
INSERT INTO articles (title, tags)
VALUES
('PostgreSQL 入门', ARRAY['database', 'postgresql']),
('Redis 入门', ARRAY['database', 'redis']);
CREATE INDEX idx_articles_tags
ON articles USING GIN (tags);
SELECT
id,
title,
cardinality(tags) AS tag_count
FROM articles
WHERE tags @> ARRAY['postgresql'];预期结果
text
id | title | tag_count
----+-----------------+-----------
1 | PostgreSQL 入门 | 2@> 表示左侧数组包含右侧数组。cardinality(tags) 返回元素总数,空数组返回 0。
常用读取与查询
使用 tags[1] 可以读取第一个元素。PostgreSQL 数组下标默认从 1 开始,这一点与多数编程语言不同。
判断某个值是否存在时,也可以写成 'postgresql' = ANY(tags)。需要把数组展开为多行时,可使用 unnest(tags),但频繁展开并关联通常说明数据模型可能需要拆分。
数组是否适合当前模型
数组适合元素数量较少、顺序或整体归属于当前记录、元素没有额外属性的场景。例如少量标签、固定候选值或简单配置项。
当数组元素需要独立增删、统计热度、关联其他表、保存创建时间或维护唯一关系时,应使用子表或多对多关联表。关联表虽然多一次连接,却能获得外键、独立索引和更清晰的数据约束。
更新与约束
可使用 array_append(tags, 'sql') 追加元素,也可使用 array_remove(tags, 'redis') 删除全部匹配值。若业务要求数组内元素不重复,需要在写入逻辑或触发器中额外保证,普通唯一约束不能直接限制单个数组内部。
按自己的节奏,确认真正理解。