PostgreSQL 除了标准 SQL 之外还提供哪些数据类型？

Question

Accepted Answer

除了标准 SQL 类型之外，PostgreSQL 提供了一套丰富的**专用数据类型** — `JSONB`、数组、`UUID`、范围、`SERIAL` 等等 — 使其对于存储结构化、半结构化或专用数据的现代应用特别有能力。

## 标准类型（带 Postgres 注记）

```sql
INTEGER, BIGINT, SMALLINT          -- integers
NUMERIC(p,s) / DECIMAL             -- exact decimals (use for money)
REAL, DOUBLE PRECISION             -- floating point
VARCHAR(n), TEXT, CHAR(n)          -- text (TEXT for unlimited)
BOOLEAN                            -- true/false (a real boolean type)
DATE, TIME, TIMESTAMP, TIMESTAMPTZ -- TIMESTAMPTZ = timezone-aware (recommended)
```

## Postgres 特有的强大类型

```sql
SERIAL / BIGSERIAL    -- auto-incrementing integer (for primary keys)
UUID                  -- universally unique id (uuid_generate_v4() / gen_random_uuid())

JSONB                 -- binary JSON — queryable, INDEXABLE, efficient (vs slower JSON)
attributes JSONB      -- store flexible/semi-structured data in a relational DB

TEXT[]                -- ARRAYS — a column holding multiple values
tags TEXT[]

INT4RANGE, TSRANGE    -- RANGE types — represent ranges (e.g. a booking time range)
period TSRANGE

INET, CIDR, MACADDR   -- network address types
POINT, POLYGON        -- geometric types (PostGIS extends this for full GIS)
ENUM                  -- custom enumerated types
```

## JSONB 和数组实战

```sql
-- JSONB — store and query structured data
INSERT INTO products (attributes) VALUES ('{"color": "red", "size": "L"}');
SELECT * FROM products WHERE attributes->>'color' = 'red';   -- extract a field
SELECT * FROM products WHERE attributes @> '{"color": "red"}';  -- containment (indexable)

-- arrays
INSERT INTO products (tags) VALUES (ARRAY['new', 'sale']);
SELECT * FROM products WHERE 'sale' = ANY(tags);
```

## 为什么这很重要

PostgreSQL 丰富的数据类型是它如此强大和受欢迎的关键原因，理解它们对于有效利用数据库很有价值。

虽然标准类型（整数、文本、日期 — 重要提示：timezone-aware 时间戳使用 **`TIMESTAMPTZ`**，金额使用 **`NUMERIC`**）涵盖基础需求，但 Postgres 的**专用类型**释放了强大能力：**`JSONB`**（二进制、可查询、*可索引* JSON）允许你在关系数据库中存储灵活的半结构化数据 — 将关系完整性与 NoSQL 般的灵活性相结合，这对现代应用（存储可变属性、设置、API 负载）极具价值，也是 Postgres 的主要优势；**数组**允许列中有多个值；**`UUID`** 适合分布式/非顺序标识符；**范围类型**优雅地表示区间（预约期间、有效范围）；专用类型（网络地址、几何/PostGIS 用于地理空间、枚举）处理特定域的数据。

理解这些类型 — 特别是 **JSONB**（Postgres 处理带查询和索引支持的半结构化数据最重要的功能之一）和数组 — 让你能为数据选择正确的类型，并利用 Postgres 的优势，而不是绕过它们。

由于丰富的类型系统是 PostgreSQL 强大的中心（同时处理传统关系和现代半结构化数据需求），理解 Postgres 特有的数据类型是宝贵的、实际相关的知识，用于设计充分利用数据库全部能力的模式 — 特别是 JSONB，用于在强大的关系系统中存储灵活结构化数据这一日益常见的需求。