15 数组与 ENUM

存储数组、查询其元素，并定义有序的 ENUM 值集合。

另外两种值得尽早了解的 PostgreSQL 专用类型。数组允许任意列存放同一类型值组成的有序列表——有时，一对多关系的范围非常明确，单独建表就有些小题大做（例如文章上的标签、用户的角色名）。ENUM 类型为列提供一组固定的命名值，由数据库强制约束，并以紧凑形式存储。二者都不是标准类型，但支持良好，在实际 Postgres 应用中广泛使用。

本章要点

声明和查询数组列
数组字面量、数组下标（从 1 开始）及实用函数
数组上的 ANY / ALL 操作符
定义和使用 ENUM 类型
何时适合使用数组/枚举，以及何时单独建表更好

示例

示例 15.1 数组与 ENUM

-- Arrays: any type can be turned into an array by appending []
DROP TABLE IF EXISTS posts_example;
CREATE TABLE posts_example (
  id   integer PRIMARY KEY,
  title text,
  tags  text[]                  -- array of text
);

INSERT INTO posts_example VALUES
  (1, 'Hello', ARRAY['intro', 'meta']),
  (2, 'SQL tips', '{tips, postgres}'),   -- shorthand literal
  (3, 'Untagged', ARRAY[]::text[]);

-- Indexing (1-based!) and length
SELECT id, title, tags[1] AS first_tag, array_length(tags, 1) AS tag_count
FROM posts_example
ORDER BY id;

-- ANY: row matches if ANY element of the array equals 'tips'
SELECT id, title FROM posts_example WHERE 'tips' = ANY(tags);

-- Array containment: @> "left contains all elements of right"
SELECT id, title FROM posts_example WHERE tags @> ARRAY['postgres'];

-- ENUM: defined type with a fixed set of allowed values
DROP TABLE IF EXISTS tickets_example;
DROP TYPE  IF EXISTS ticket_status;
CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'closed');

CREATE TABLE tickets_example (
  id     integer PRIMARY KEY,
  title  text,
  status ticket_status NOT NULL DEFAULT 'open'
);
INSERT INTO tickets_example (id, title, status) VALUES (1, 'Bug 1', 'open'), (2, 'Bug 2', 'closed');

SELECT * FROM tickets_example ORDER BY id;

PostgreSQL 中的数组是一等公民：可以为它们建立索引，在 WHERE 子句中使用，用 ARRAY[] 构造，用 unnest() 将其展开成行，并用 @>、<@ 查询包含关系，用 && 查询重叠。它们以紧凑形式内联存储（非常大的数组则使用超长属性存储技术（TOAST））。

最重要的一个特殊之处是：数组下标默认从 1 开始。这里的 tags[1] 是数组中的第一个元素，不过 PostgreSQL 也支持显式指定下界。'{a, b, c}' 这种字面量形式是字符串形式的字面量简写；ARRAY['a', 'b', 'c'] 是构造器——两者生成相同的值。

ENUM 类型非常适合这样的场景：允许值集合已知且规模较小，并且应由数据库强制约束（'open' | 'in_progress' | 'closed'、'small' | 'medium' | 'large'）。无论标签长度如何，它们都以 4 字节存储，并且按声明顺序而不是字母顺序排序——对于状态工作流来说很有用，因为 'open' < 'in_progress' < 'closed' 正是自然顺序。

运行示例

运行方式：

$ psql -f source/arrays-and-enum.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 3
 id |  title   | first_tag | tag_count
----+----------+-----------+-----------
  1 | Hello    | intro     |         2
  2 | SQL tips | tips      |         2
  3 | Untagged |           |
(3 rows)
 ...

常见陷阱

使用其他语言的开发者很容易在从 1 开始的数组下标上栽跟头。tags[0] 的结果是 NULL，而不是报错。
修改 ENUM（添加一个值）需要使用 ALTER TYPE；不重建类型，删除一个值基本上不可能。对于经常变化的集合，优先使用通过外键连接的小型查找表。
数组看起来很适合“我只需要一个列表”的场景——但只要你需要单独查询、连接、排序或分别约束其中的元素，另建一张带外键的表几乎总是更清晰。只有当元素属于该行、而不是独立实体时，才使用数组。
tag = 'x' 在数组列上不起作用——使用 'x' = ANY(tag) 或 tag @> ARRAY['x']。

提示

使用 GIN 为数组建立索引：CREATE INDEX ON posts_example USING gin (tags); 可让 @>、<@ 和 && 查询在 tags 列上快速执行。没有这个索引时，这些查询会扫描整张表。

动手练习

添加一行 tags = ARRAY['intro','postgres','tips']，然后运行 SELECT id, tag FROM posts_example, unnest(tags) AS tag;，将每个标签展开为一行。使用 ALTER TYPE ticket_status ADD VALUE 'reopened'; 添加一个新的 ENUM 值，再插入一条具有该状态的工单。

15 数组与 ENUM

本章要点

示例

运行示例

常见陷阱

下载