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') 删除全部匹配值。若业务要求数组内元素不重复,需要在写入逻辑或触发器中额外保证,普通唯一约束不能直接限制单个数组内部。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

数组类型

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通