PostgreSQL 中的数组列如何工作？

Question

Accepted Answer

PostgreSQL 支持**数组**列——单个列可以包含*相同类型的多个值*。这是 Postgres 的一个独特功能，可用于存储列表（标签、角色、分数），无需单独的表，并具有用于查询的运算符和函数。

## 定义和插入数组

```sql
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  tags TEXT[]                         -- an array of text
);

INSERT INTO posts (title, tags) VALUES
  ('Hello', ARRAY['ruby', 'web']),    -- ARRAY[] syntax
  ('World', '{"news", "tech"}');       -- or curly-brace literal
```

`type[]` 语法声明一个数组列。你可以使用 `ARRAY[...]` 或 `'{...}'` 字面值语法进行插入。

## 查询数组

```sql
-- ANY — does the array contain a value?
SELECT * FROM posts WHERE 'ruby' = ANY(tags);

-- @> containment — does the array contain ALL of these?
SELECT * FROM posts WHERE tags @> ARRAY['ruby', 'web'];

-- && overlap — do the arrays share ANY element?
SELECT * FROM posts WHERE tags && ARRAY['tech', 'news'];

-- access by index (1-based!) and length
SELECT tags[1], array_length(tags, 1) FROM posts;

-- unnest — expand an array into rows
SELECT id, unnest(tags) AS tag FROM posts;   -- one row per tag
```

运算符如 `ANY`（包含一个值）、`@>`（包含）和 `&&`（重叠），以及函数如 `unnest`（展开为行）和 `array_agg`（将行聚合为数组），使数组可查询。

## 索引数组（GIN）

```sql
-- a GIN index makes array containment/membership queries fast
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
-- now WHERE tags @> ARRAY['ruby'] uses the index
```

## 何时使用数组（以及何时不使用）

```text
✓ USE for: simple lists tightly coupled to the row (tags, roles, a few scores)
  where you don't need to query/join the individual values much
✗ AVOID when: you need to query, join, or maintain the values relationally,
  or the list is large/complex → a separate related table (normalized) is better
→ Arrays are convenient but can violate normalization; use them judiciously.
```

## 为什么这很重要

数组列是 PostgreSQL 的一个独特且有用的功能，理解它们对于利用 Postgres 的能力和做出合理的设计决策都很有价值。

数组使你能够在**单个列中存储多个值**（标签、角色、列表），无需单独的表——对于与行紧密耦合的简单列表很方便。

了解如何定义、插入，尤其是**查询**数组（如 `ANY` 用于成员检查、`@>` 用于包含性、`&&` 用于重叠的运算符，以及 `unnest` 等函数将数组展开为行）对于有效使用它们是必要的，而了解 **GIN 索引**使数组查询高效这一点对性能很重要。

同样重要的是**设计判断**，即何时使用数组与规范化的关联表：数组适用于你不需要查询、联接或关系维护的简单列表，但它们可能会违反规范化原则，当你需要查询单个值、对其联接或管理复杂/大型列表时会变得尴尬——在这种情况下，单独的关联表是更好的、规范化的选择。

理解这种权衡（数组用于简单耦合列表的便利性，单独的表用于关系管理的数据）反映了合理的设计判断。

由于数组是一个有用的、独特的 Postgres 功能，可以简化某些设计（避免为简单列表创建联接表），但也可能被滥用（非规范化应该是关系型的数据），因此理解数组列——如何定义、查询和索引它们，以及至关重要的是何时它们是合适的而何时规范化表更好——是为 Postgres 模式设计提供的有价值的、实际相关的知识，该设计对每种多值数据使用正确的方法。