pgsql-13 数组与范围类型

2025-09-25 约 1635 字预计阅读 4 分钟

1. 概述

数组和范围类型是 PostgreSQL 相对 MySQL 的独特优势。它们让某些原本需要额外关联表或复杂应用逻辑的场景，用一个字段就能优雅解决。

对比 MySQL：MySQL 没有原生数组类型（只能用 JSON 数组或逗号分隔字符串模拟，无法高效索引/查询），也没有范围类型。这两个类型是 PG 数据建模能力更强的体现。

2. 数组类型

2.1 定义与写入

CREATE TABLE articles (
    id      SERIAL PRIMARY KEY,
    title   TEXT,
    tags    TEXT[],           -- 一维文本数组
    ratings INTEGER[],        -- 整数数组
    matrix  INTEGER[][]       -- 多维数组
);

-- 两种字面量写法
INSERT INTO articles (title, tags, ratings) VALUES
    ('PostgreSQL 教程', ARRAY['database','postgresql'], ARRAY[5,4,5]),
    ('SQL 指南',        '{"sql","tutorial"}',           '{4,5,4}');

2.2 访问与操作（注意：下标从 1 开始）

SELECT tags[1] FROM articles;              -- 第一个元素(PG数组下标从1开始!)
SELECT tags[1:2] FROM articles;            -- 切片
SELECT array_length(tags, 1) FROM articles; -- 第1维长度
SELECT cardinality(tags) FROM articles;    -- 元素总数

-- 修改
UPDATE articles SET tags = array_append(tags, 'new');   -- 追加
UPDATE articles SET tags = array_remove(tags, 'sql');   -- 删除元素
UPDATE articles SET tags = tags || ARRAY['a','b'];      -- 拼接

2.3 包含/查询操作符

SELECT * FROM articles WHERE 'postgresql' = ANY(tags);   -- 含某元素
SELECT * FROM articles WHERE tags @> ARRAY['sql'];       -- 包含(数组含子集)
SELECT * FROM articles WHERE tags && ARRAY['sql','go'];  -- 有交集(任一匹配)
SELECT * FROM articles WHERE 5 = ALL(ratings);           -- 全部满足

2.4 展开与聚合

-- unnest：数组转行
SELECT id, unnest(tags) AS tag FROM articles;

-- array_agg：行转数组（分组聚合）
SELECT array_agg(title) FROM articles;
SELECT room_id, array_agg(id) FROM bookings GROUP BY room_id;

2.5 数组索引（GIN）

-- 让包含查询(@>, &&, =ANY)走索引
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];  -- 命中索引

这正是数组相对"逗号分隔字符串"的关键优势：可以用 GIN 索引高效查询"包含某标签"，而字符串 LIKE '%tag%' 既慢又不准。

3. 范围类型

范围类型表示一个连续区间，内置几种，也可自定义。

类型	元素
`int4range` / `int8range`	整数范围
`numrange`	数值范围
`tsrange` / `tstzrange`	时间戳范围（后者带时区）
`daterange`	日期范围

3.1 边界表示

-- 方括号=闭区间(含端点)，圆括号=开区间(不含端点)
SELECT int4range(1, 10, '[]');    -- 1到10，两端都含
SELECT daterange('2026-07-01', '2026-08-01', '[)'); -- 含7-1不含8-1(半开，最常用)
SELECT numrange(1.0, NULL);       -- 无上界(1到正无穷)

3.2 范围操作符

SELECT int4range(1,10) @> 5;                      -- 包含某值 → true
SELECT int4range(1,10) @> int4range(2,5);         -- 包含子范围
SELECT int4range(1,10) && int4range(5,15);        -- 是否重叠(&&) → true
SELECT int4range(1,5) -|- int4range(5,10);        -- 是否相邻 → true
SELECT lower(int4range(1,10)), upper(int4range(1,10)); -- 取下/上界

3.3 实战：会议室预订防重叠（杀手锏）

用范围类型 + GiST 排他约束，在数据库层原子地防止时间段重叠——这是 MySQL 无法用约束实现的：

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
    id      SERIAL PRIMARY KEY,
    room_id INTEGER,
    during  TSRANGE,
    -- 同一房间(room_id相等)、时间段重叠(&&) 的两条记录不允许共存
    EXCLUDE USING GiST (room_id WITH =, during WITH &&)
);

INSERT INTO room_bookings (room_id, during)
    VALUES (101, tsrange('2026-07-01 09:00', '2026-07-01 10:00'));   -- OK
INSERT INTO room_bookings (room_id, during)
    VALUES (101, tsrange('2026-07-01 09:30', '2026-07-01 11:00'));   -- 报错：与上一条重叠

若不用范围类型，只能用两列 start_time/end_time 存，且无法用约束防重叠，得在应用层查询+加锁判断（有并发漏洞）。范围类型 + 排他约束是 PG 数据建模的经典优势。约束见约束详解。

3.4 范围查询与索引

-- GiST 索引加速范围重叠/包含查询
CREATE INDEX idx_bookings_during ON room_bookings USING GiST (during);

SELECT * FROM room_bookings WHERE during @> now();   -- 当前正在使用中的预订
SELECT * FROM room_bookings
WHERE during && tsrange('2026-07-01', '2026-07-02');  -- 某天有预订的

4. 使用建议

数组适合元素数量不多、无需单独维护属性的场景（标签、权限列表）；若元素本身是复杂实体、需增删改查/关联，仍应用关联表。
范围类型 + 排他约束是"防重叠"类需求（预订、排班、价格生效期）的最优解。
两者的包含/重叠查询记得配 GIN（数组）/ GiST（范围）索引。

5. 面试高频问答

Q1：PostgreSQL 的数组相比 MySQL 用逗号分隔字符串有什么优势？ 数组是原生类型，支持下标访问、包含/交集操作符，且能用 GIN 索引高效查询"包含某元素"；逗号分隔字符串只能 LIKE 模糊匹配，慢且不准。

Q2：数组下标从几开始？ 从 1 开始（不是 0），这是 PG 数组的常见坑。

Q3：范围类型能解决什么经典问题？ 表示连续区间（时间、数值），配合 GiST 排他约束可在数据库层原子地防止重叠（如会议室预订、排班不冲突），这是 MySQL 用约束做不到的。

Q4：数组和范围类型分别用什么索引？ 数组用 GIN（加速 @>、&&、=ANY 等包含查询）；范围用 GiST（加速 @>、&& 等重叠/包含查询）。

Q5：什么时候该用数组，什么时候该用关联表？ 元素少、只作标记且不需单独增删改查用数组；元素是有属性的实体、需关联和维护时用关联表。

6. 下一步

学习全文搜索，掌握文本检索能力。

目录