# 数组与 ENUM

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

---

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 本章要点 {#learn}

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

## 示例 {#example}

```sql {num="15.1" caption="数组与 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'` 正是自然顺序。

## 运行示例 {#run}

运行方式：

```text
$ 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)
 ...
```

## 常见陷阱 {#pitfalls}

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

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

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

## 下载 {#downloads}

SQL 文件：[arrays-and-enum.sql](/examples/source/arrays-and-enum.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)