PostgreSQL数据库技术第 5 篇：数据类型与高级特性

摘要

本文深入探讨PostgreSQL数据库的特色数据类型与高级功能特性。PostgreSQL作为最先进的开源关系型数据库，提供了丰富多样的数据类型支持，远超传统SQL标准。文章将系统讲解数组类型、JSON/JSONB数据处理、范围类型、全文检索、枚举类型、复合类型以及UUID等特色数据类型的使用方法和应用场景。通过本文学习，读者将掌握如何利用PostgreSQL的高级数据类型解决实际业务中的复杂数据存储和查询需求，提升数据库设计的灵活性和查询效率。

学习目标

理解并掌握PostgreSQL数组类型的创建、操作和索引方法
深入理解JSON与JSONB的区别，灵活运用JSON函数进行数据处理
掌握范围类型的概念及应用场景，实现区间数据的存储和查询
理解全文检索原理，能够构建高效的全文搜索系统
掌握枚举类型、复合类型等自定义类型的创建和使用
了解UUID及其他特殊数据类型的应用场景

一、数组类型

1.1 数组类型概述

PostgreSQL原生支持数组类型，这是其最具特色的特性之一。与传统关系型数据库需要通过关联表来存储多值属性不同，PostgreSQL允许直接在单个字段中存储数组数据。这种设计大大简化了数据模型，特别适合存储标签列表、历史记录、多选结果等场景。

数组类型可以是任何内置数据类型的数组，包括基本类型（整数、字符串、日期）和复杂类型（自定义类型、复合类型）。数组可以是一维的，也可以是多维的，这为存储矩阵、张量等复杂数据结构提供了便利。

从存储角度来看，PostgreSQL数组采用变长存储方式，数组长度可以动态变化。每个数组元素占据与其数据类型相对应的存储空间，数组本身还存储了维度信息和长度信息。对于固定长度的数组（如指定了维度的数组），PostgreSQL会在存储时进行优化。

1.2 数组的创建与初始化

创建包含数组字段的表时，需要使用方括号语法指定数组类型。以下示例展示了如何创建包含数组字段的表：

-- 创建包含数组字段的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    tags TEXT[],              -- 文本数组，一维
    categories INTEGER[],     -- 整数数组
    prices NUMERIC(10,2)[],   -- 价格数组
    stock_locations INTEGER[3][3], -- 固定大小的二维数组
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 插入数组数据
INSERT INTO products (name, tags, categories, prices, stock_locations)
VALUES (
    '智能手表',
    ARRAY['电子产品', '可穿戴', '智能设备'],  -- 使用ARRAY构造函数
    ARRAY[1, 5, 12],
    ARRAY[299.99, 279.99, 259.99],
    ARRAY[[1,2,3], [4,5,6], [7,8,9]]  -- 二维数组
);
 
-- 使用简化的数组字面量语法
INSERT INTO products (name, tags, categories)
VALUES (
    '蓝牙耳机',
    '{电子,音频,无线}',  -- 使用花括号语法
    '{3,8,15}'
);

数组元素的访问和操作提供了多种方法。PostgreSQL使用从1开始的索引系统（符合SQL标准习惯，与许多编程语言的0索引不同），可以通过下标运算符访问特定位置的元素。

-- 访问数组元素
SELECT name, tags[1] AS first_tag,       -- 获取第一个标签
       tags[2] AS second_tag,
       prices[1] AS current_price
FROM products
WHERE id = 1;
 
-- 使用ANY运算符检查数组是否包含某个值
SELECT name, tags
FROM products
WHERE '电子产品' = ANY(tags);
 
-- 使用ALL运算符检查所有元素是否满足条件
SELECT name
FROM products
WHERE 100 < ALL(prices);  -- 所有价格都大于100

1.3 数组操作函数与运算符

PostgreSQL提供了丰富的数组操作函数和运算符，使数组处理变得简单高效。这些工具涵盖了数组的查询、修改、扩展和聚合等各个方面。

数组运算符表

运算符	描述	示例	结果
`=`	相等比较	ARRAY[1,2] = ARRAY[1,2]	true
`<>`	不等比较	ARRAY[1,2] <> ARRAY[1,3]	true
`<`	小于比较	ARRAY[1,2] < ARRAY[1,3]	true
`>`	大于比较	ARRAY[1,2] > ARRAY[1,1]	true
`<=`	小于等于	ARRAY[1,2] ⇐ ARRAY[1,2]	true
`>=`	大于等于	ARRAY[1,2] >= ARRAY[1,2]	true
`@>`	包含	ARRAY[1,2,3] @> ARRAY[1,2]	true
`<@`	被包含	ARRAY[1,2] <@ ARRAY[1,2,3]	true
`&&`	重叠（有交集）	ARRAY[1,2] && ARRAY[2,3]	true
`		`	连接

常用数组函数

-- array_length() 获取数组长度
SELECT name,
       array_length(tags, 1) AS tag_count,  -- 一维长度
       array_length(stock_locations, 1) AS rows,    -- 二维数组行数
       array_length(stock_locations, 2) AS columns  -- 二维数组列数
FROM products;
 
-- array_append() 和 array_prepend() 添加元素
UPDATE products
SET tags = array_append(tags, '新品')  -- 在末尾添加
WHERE id = 1;
 
UPDATE products
SET tags = array_prepend('促销', tags)  -- 在开头添加
WHERE id = 1;
 
-- array_cat() 连接数组
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);  -- 结果: {1,2,3,4}
 
-- unnest() 将数组展开为行
SELECT name, unnest(tags) AS tag
FROM products
WHERE id = 1;
 
-- array_position() 查找元素位置
SELECT array_position(ARRAY[1,2,3,4], 3);  -- 结果: 3
 
-- array_remove() 移除元素
SELECT array_remove(ARRAY[1,2,2,3], 2);  -- 结果: {1,3}
 
-- array_distinct() 去重（PostgreSQL 13+）
SELECT array_distinct(ARRAY[1,2,2,3,3,3]);  -- 结果: {1,2,3}

1.4 数组索引策略

为了提高数组查询的性能，PostgreSQL提供了专门针对数组的索引类型。最常用的是GIN索引（广义倒排索引），它可以加速数组元素的包含查询。

-- 创建GIN索引加速数组查询
CREATE INDEX idx_products_tags_gin ON products USING GIN (tags);
 
-- 创建带有特定操作符类的GIN索引
CREATE INDEX idx_products_tags_gin_ops ON products USING GIN (tags array_ops);
 
-- 创建GiST索引（适用于某些特定场景）
CREATE INDEX idx_products_tags_gist ON products USING GIST (tags);
 
-- 现在查询包含特定标签的产品会很快
SELECT name, tags
FROM products
WHERE tags @> ARRAY['电子产品'];  -- 利用GIN索引

GIN索引通过为每个数组元素创建索引项，使得包含查询、重叠查询等操作能够快速执行。对于大型数组或者频繁进行数组查询的场景，GIN索引能带来显著的性能提升。

1.5 数组类型应用场景图解

flowchart TD
    A[数组类型应用决策] --> B{数据特征分析}
    B --> C[多值属性]
    B --> D[有序数据]
    B --> E[矩阵数据]

    C --> C1[标签系统]
    C --> C2[分类体系]
    C --> C3[多选结果]

    D --> D1[历史记录]
    D --> D2[时间序列]
    D --> D3[排名列表]

    E --> E1[坐标数据]
    E --> E2[多维评分]
    E --> E3[科学计算]

    C1 --> F[TEXT[]类型]
    C2 --> F
    C3 --> G[INTEGER[]类型]

    D1 --> H[TIMESTAMP[]类型]
    D2 --> H
    D3 --> G

    E1 --> I[多维数组<br/>INTEGER[N][N]]
    E2 --> I
    E3 --> I

    F --> J[优势<br/>• 单表存储<br/>• 简化关联<br/>• 原子操作]
    G --> J
    H --> J
    I --> K[优势<br/>• 矩阵运算<br/>• 科学计算<br/>• 空间索引]

    J --> L[注意事项<br/>• 避免过大数组<br/>• 考虑GIN索引<br/>• 评估规范化需求]
    K --> L

图表说明：此流程图展示了数组类型在不同应用场景下的选择策略。左侧从数据特征分析开始，将应用场景分为多值属性、有序数据和矩阵数据三大类。中间部分具体展示了每个类别下的典型应用案例。右侧部分则根据应用场景推荐合适的数组类型，并说明了各种类型的优势和注意事项。这种可视化帮助开发者在面对实际业务需求时，能够快速判断是否应该使用数组类型以及如何选择合适的数组类型。

1.6 数组类型的最佳实践

使用数组类型时需要权衡便利性与数据规范性。数组类型简化了某些场景的数据模型，但也可能带来数据更新和查询的复杂性。

适用场景：

标签系统：文章标签、商品标签、用户兴趣标签
历史记录：最近访问记录、操作历史轨迹
多值属性：多个分类、多个联系方式
矩阵数据：科学计算、图像处理、空间数据

不适用场景：

需要频繁修改的关联关系（传统外键更合适）
需要对关联数据进行复杂查询和统计
数组元素数量不确定且可能非常大（数千个元素）

二、JSON与JSONB数据类型

2.1 JSON与JSONB的区别

PostgreSQL提供了两种JSON数据类型：JSON和JSONB。虽然它们都用于存储JSON格式数据，但在存储方式和处理机制上存在重要差异。

JSON类型：将输入的JSON文本原样存储，保留空格、键的顺序等格式信息。查询时需要每次解析文本，因此插入速度较快但查询速度相对较慢。适合需要保留原始JSON格式或很少需要查询JSON内部字段的场景。

JSONB类型：将JSON数据解析为二进制格式存储，去除了空白字符，对键进行排序，并建立了内部索引结构。插入时需要解析开销，但查询速度显著更快。适合需要频繁查询JSON内部字段或进行JSON操作的场景。

2.2 JSON/JSONB数据操作

-- 创建包含JSON字段的表
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    profile JSONB,              -- JSONB类型，推荐使用
    metadata JSON,              -- JSON类型，保留原始格式
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 插入JSON数据
INSERT INTO user_profiles (user_id, profile, metadata)
VALUES (
    1001,
    '{"name": "张三", "age": 28, "city": "北京", "hobbies": ["阅读", "旅行"]}'::JSONB,
    '{"source": "web", "version": "1.0"}'::JSON
);
 
-- 插入使用JSONB构造函数
INSERT INTO user_profiles (user_id, profile)
VALUES (
    1002,
    jsonb_build_object(
        'name', '李四',
        'age', 32,
        'city', '上海',
        'hobbies', jsonb_build_array('摄影', '音乐', '健身')
    )
);

2.3 JSONB查询与操作

JSONB类型提供了丰富的查询和操作功能，可以通过键路径访问嵌套字段，使用各种操作符和函数进行JSON数据的处理和转换。

-- 访问JSON字段
SELECT user_id,
       profile->>'name' AS name,          -- ->> 获取文本值
       profile->>'age' AS age,
       profile->'hobbies' AS hobbies      -- -> 获取JSONB对象
FROM user_profiles;
 
-- 访问嵌套字段
SELECT user_id,
       profile->'address'->>'city' AS city
FROM user_profiles
WHERE profile ? 'address';  -- 检查是否存在某个键
 
-- 使用路径操作符（PostgreSQL 12+）
SELECT user_id,
       profile #>> '{address,city}' AS city
FROM user_profiles;
 
-- JSONB包含查询
SELECT user_id, profile
FROM user_profiles
WHERE profile @> '{"age": 28}';  -- 包含指定的键值对
 
-- 查询数组包含某元素
SELECT user_id, profile
FROM user_profiles
WHERE profile->'hobbies' @> '["阅读"]'::jsonb;
 
-- 修改JSONB数据
UPDATE user_profiles
SET profile = profile || '{"email": "[email protected]"}'  -- 添加字段
WHERE user_id = 1001;
 
UPDATE user_profiles
SET profile = profile - 'age'  -- 删除字段
WHERE user_id = 1001;
 
-- 使用jsonb_set函数深度修改
UPDATE user_profiles
SET profile = jsonb_set(
    profile,
    '{address,city}',
    '"深圳"'
)
WHERE user_id = 1001;

2.4 JSONB操作符与函数速查表

JSONB操作符表

操作符	描述	示例	结果类型
`->`	获取JSON对象字段或数组元素	`data->'key'`	jsonb
`->>`	获取JSON对象字段或数组元素为文本	`data->>'key'`	text
`#>`	获取指定路径的JSON对象	`data#>'{a,b}'`	jsonb
`#>>`	获取指定路径的JSON对象为文本	`data#>>'{a,b}'`	text
`@>`	左边JSON包含右边JSON	`data @> '{"key": "value"}'`	boolean
`<@`	左边JSON被右边JSON包含	`'{"key": "value"}' <@ data`	boolean
`?`	键/元素字符串是否存在	`data ? 'key'`	boolean
`?&`	是否所有键都存在	`data ?& array['key1','key2']`	boolean
`?\|`	是否存在任意一个键	`data ?\| array['key1','key2']`	boolean
`		`	连接JSON
`-`	删除键或元素	`data - 'key'`	jsonb

2.5 JSONB索引策略

JSONB字段可以创建多种类型的索引，根据查询模式选择合适的索引类型对性能至关重要。

-- 默认GIN索引（支持@>、?、?&等操作符）
CREATE INDEX idx_user_profiles_profile_gin ON user_profiles USING GIN (profile);
 
-- jsonb_path_ops操作符类（仅支持@>，但索引更小更快）
CREATE INDEX idx_user_profiles_profile_path ON user_profiles USING GIN (profile jsonb_path_ops);
 
-- 对特定字段创建表达式索引
CREATE INDEX idx_user_profiles_name ON user_profiles ((profile->>'name'));
 
-- 对嵌套字段创建表达式索引
CREATE INDEX idx_user_profiles_city ON user_profiles ((profile->>'address'->>'city'));
 
-- 唯一索引（基于JSON字段值）
CREATE UNIQUE INDEX idx_user_profiles_email ON user_profiles ((profile->>'email'));

2.6 JSON数据类型选择决策图

flowchart TD
    A[JSON数据类型选择] --> B{数据使用模式}
    B --> C[主要是存储]
    B --> D[需要查询]

    C --> E{需要保留格式}
    E --> F[JSON类型<br/>✓ 保留空格和顺序<br/>✓ 插入快速<br/>✓ 存储紧凑]
    E --> G[处理日志文件<br/>原始API响应<br/>配置文件备份]

    D --> H{查询频率}
    H --> I[偶尔查询]
    H --> J[频繁查询]

    I --> K[JSON类型<br/>简单场景够用]

    J --> L{查询复杂度}
    L --> M[简单键值查询]
    L --> N[复杂嵌套查询]

    M --> O[JSONB类型<br/>✓ 查询快速<br/>✓ 完整索引支持<br/>✓ 丰富操作符]

    N --> O

    F --> P[推荐场景<br/>• 日志记录<br/>• 原始数据存储<br/>• 格式敏感数据]

    O --> Q[推荐场景<br/>• 用户配置<br/>• 动态属性<br/>• 复杂查询需求<br/>• 数据分析]

    P --> R[数据迁移建议<br/>JSON → JSONB<br/>评估后可升级]

    Q --> R

图表说明：此决策图详细展示了在选择JSON或JSONB类型时的思考路径。首先判断数据是主要用于存储还是需要查询。对于主要用于存储的数据，如果需要保留原始格式（如日志文件），JSON类型是合适的选择。对于需要查询的数据，根据查询频率和复杂度，大多数情况下JSONB类型是更好的选择，因为它提供了完整的索引支持和丰富的操作符。图中还列出了各自的推荐场景，帮助开发者快速做出决策。

三、范围类型

3.1 范围类型概述

范围类型（Range Types）是PostgreSQL的特色数据类型，用于表示连续区间的值。范围类型特别适合存储时间区间、价格区间、数值范围等数据。使用范围类型可以大大简化区间查询和范围比较操作。

PostgreSQL内置了以下范围类型：

int4range：整数范围
int8range：大整数范围
numrange：数值范围
tsrange：无时区时间戳范围
tstzrange：有时区时间戳范围
daterange：日期范围

3.2 范围类型的创建与使用

-- 创建包含范围字段的表
CREATE TABLE price_ranges (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price_range NUMRANGE,        -- 数值范围
    valid_daterange DATERANGE,   -- 日期范围
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 插入范围数据
INSERT INTO price_ranges (product_name, price_range, valid_daterange)
VALUES (
    '高级套餐',
    '[100, 500]',               -- 包含端点的闭区间
    '[2024-01-01, 2024-12-31]'
);
 
-- 使用范围构造函数
INSERT INTO price_ranges (product_name, price_range)
VALUES (
    '标准套餐',
    numrange(50, 200, '[]')     -- 使用构造函数，'[]'表示包含两端
);
 
-- 半开区间
INSERT INTO price_ranges (product_name, price_range)
VALUES (
    '基础套餐',
    '[0, 100)'                  -- 包含下限，不包含上限
);
 
-- 无限范围
INSERT INTO price_ranges (product_name, price_range)
VALUES (
    '豪华套餐',
    '[500,)'                    -- 从500到正无穷
);

3.3 范围查询与操作

范围类型提供了丰富的操作符，使得范围查询和比较变得简单直观。

-- 查询包含特定值的价格范围
SELECT product_name, price_range
FROM price_ranges
WHERE price_range @> 150;  -- @> 表示包含
 
-- 查询被某个范围包含的价格范围
SELECT product_name, price_range
FROM price_ranges
WHERE price_range <@ '[0, 1000]';
 
-- 查询与某个范围重叠的价格范围
SELECT product_name, price_range
FROM price_ranges
WHERE price_range && '[100, 300]';
 
-- 查询左侧相邻
SELECT product_name, price_range
FROM price_ranges
WHERE price_range -|- '[100, 200]';
 
-- 访问范围的上下界
SELECT product_name,
       lower(price_range) AS min_price,
       upper(price_range) AS max_price,
       lower_inc(price_range) AS include_lower,  -- 是否包含下界
       upper_inc(price_range) AS include_upper   -- 是否包含上界
FROM price_ranges;
 
-- 范围合并
SELECT numrange('[1,10]') + numrange('[5,15]');  -- 结果: [1,15]
 
-- 范围相减
SELECT numrange('[1,10]') - numrange('[5,7]');   -- 结果: [1,5) + (7,10]
 
-- 判断范围是否为空
SELECT isempty(numrange('(1,1)'));  -- 结果: true（空范围）

3.4 范围类型操作符表

操作符	描述	示例	结果
`@>`	包含	`[1,10] @> 5`	true
`<@`	被包含	`[5,6] <@ [1,10]`	true
`&&`	重叠	`[1,5] && [3,7]`	true
`<<`	严格左邻	`[1,3] << [5,7]`	true
`>>`	严格右邻	`[5,7] >> [1,3]`	true
`&<`	不延伸到右侧	`[1,3] &< [3,5]`	true
`&>`	不延伸到左侧	`[3,5] &> [1,3]`	true
`-	-`	相邻	`[1,3] -
`+`	合并	`[1,3] + [3,5]`	`[1,5]`
`-`	相减	`[1,5] - [3,4]`	`[1,3) + (4,5]`

3.5 多范围类型

PostgreSQL 14及以上版本支持多范围类型（Multirange Types），允许存储多个不连续的范围。

-- 创建包含多范围字段的表
CREATE TABLE schedules (
    id SERIAL PRIMARY KEY,
    resource_name VARCHAR(100),
    available_times TSMULTIRANGE,  -- 时间戳多范围
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 插入多范围数据（工作时间段）
INSERT INTO schedules (resource_name, available_times)
VALUES (
    '会议室A',
    tsmultirange(
        tsrange('2024-01-01 09:00:00', '2024-01-01 12:00:00'),
        tsrange('2024-01-01 14:00:00', '2024-01-01 18:00:00')
    )
);
 
-- 添加更多时间段
UPDATE schedules
SET available_times = available_times + tsrange('2024-01-02 09:00:00', '2024-01-02 17:00:00')
WHERE id = 1;
 
-- 查询与特定时间段重叠的资源
SELECT resource_name, available_times
FROM schedules
WHERE available_times && tsrange('2024-01-01 10:00:00', '2024-01-01 11:00:00');

3.6 范围类型应用场景图解

sequenceDiagram
    autonumber
    participant User as 用户/应用
    participant App as 应用程序
    participant DB as PostgreSQL

    Note over User,DB: 范围类型典型应用：酒店房间预订

    User->>App: 1. 查询2024-03-01可用的房间
    App->>DB: 2. SELECT room_id<br/>FROM bookings<br/>WHERE NOT (booking_range<br/>&& @daterange)

    DB->>DB: 3. 范围重叠检查<br/>利用GiST索引

    DB-->>App: 4. 返回可用房间列表

    User->>App: 5. 预订房间<br/>2024-03-01至2024-03-05

    App->>DB: 6. INSERT INTO bookings<br/>(room_id, booking_range)<br/>VALUES (101,<br/>daterange(2024-03-01,<br/>2024-03-05))

    DB->>DB: 7. 检查范围约束<br/>确保无重叠

    DB-->>App: 8. 预订成功

    User->>App: 9. 查询预订历史
    App->>DB: 10. SELECT booking_range<br/>FROM bookings<br/>WHERE room_id=101<br/>ORDER BY booking_range

    DB-->>App: 11. 返回时间范围列表
    App-->>User: 12. 显示预订历史

图表说明：此序列图展示了范围类型在酒店房间预订系统中的典型应用流程。用户查询可用房间时，数据库利用范围类型的重叠操作符（&&）和GiST索引快速排除已被预订的房间。预订时，数据库使用范围约束自动检查时间冲突，避免重叠预订。最后，查询预订历史时，可以直接对范围字段进行排序。这个流程图清楚地展示了范围类型如何简化区间数据的存储和查询逻辑。

四、全文检索

4.1 全文检索原理

全文检索（Full-Text Search）是一种用于在文本数据中进行高效搜索的技术。与传统的LIKE模式匹配不同，全文检索通过文本预处理、分词、建立索引等步骤，实现快速、智能的文本搜索。

PostgreSQL的全文检索系统基于以下核心概念：

文档（Document）：被搜索的文本单元，可以是表中的某个字段或多个字段的组合。

词干提取（Stemming）：将单词还原为其词干形式，如”running”→“run”，“cats”→“cat”。

停止词（Stop Words）：常见的无意义词汇（如”the”、“a”、“is”），搜索时会被忽略。

词位（Lexeme）：经过标准化处理的词汇单元，是全文检索索引的基本单位。

文本搜索配置（Text Search Configuration）：定义如何解析和处理文本的规则集，包括分词器和字典。

4.2 全文检索数据类型

PostgreSQL提供了两种专门用于全文检索的数据类型：

tsvector：存储经过处理的文档，包含词位列表及其位置信息。适用于需要频繁搜索的文档。

tsquery：存储搜索查询，包含搜索词及其布尔组合逻辑。

-- 创建包含全文检索字段的表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    content_tsv TSVECTOR,              -- 存储预处理后的文档向量
    search_vector TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))
    ) STORED,  -- 自动生成的全文检索向量
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 创建GIN索引加速全文检索
CREATE INDEX idx_articles_content_tsv ON articles USING GIN (content_tsv);
CREATE INDEX idx_articles_search_vector ON articles USING GIN (search_vector);

4.3 全文检索基本操作

-- 将文本转换为tsvector
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 结果: 'brown':3 'dog':9 'fox':4 'jump':6 'lazi':8 'quick':2
 
-- 创建tsquery查询
SELECT to_tsquery('english', 'Fox & Dog');
-- 结果: 'fox' & 'dog'
 
-- 执行全文检索查询
SELECT title, content
FROM articles
WHERE content_tsv @@ to_tsquery('english', 'database & optimization');
 
-- 使用plainto_tsquery（更简单的查询语法）
SELECT title, content
FROM articles
WHERE content_tsv @@ plainto_tsquery('english', 'database optimization');
-- 自动处理AND/OR逻辑
 
-- 带排序的全文检索（按相关性排序）
SELECT title,
       ts_rank(content_tsv, query) AS rank
FROM articles,
     to_tsquery('english', 'database performance') AS query
WHERE content_tsv @@ query
ORDER BY rank DESC;
 
-- 高亮搜索结果
SELECT title,
       ts_headline('english', content, to_tsquery('english', 'database'))
FROM articles
WHERE content_tsv @@ to_tsquery('english', 'database');

4.4 文本搜索配置

PostgreSQL支持多种语言的文本搜索配置，每种配置使用特定的分词器和字典。

-- 查看可用的文本搜索配置
SELECT cfgname FROM pg_ts_config;
 
-- 使用不同语言的配置
SELECT to_tsvector('english', 'database optimization');
SELECT to_tsvector('simple', 'database optimization');  -- 不做词干提取
SELECT to_tsvector('chinese', '数据库优化');
 
-- 创建自定义文本搜索配置
CREATE TEXT SEARCH CONFIGURATION mycopy (COPY = english);
 
-- 添加自定义字典
ALTER TEXT SEARCH CONFIGURATION mycopy
    ALTER MAPPING FOR asciiword WITH english_stem, mysynonym;

4.5 全文检索高级功能

-- 前缀搜索
SELECT title
FROM articles
WHERE content_tsv @@ to_tsquery('english', 'optim:*');
 
-- 短语搜索（需要tsquery_phrase操作符类）
SELECT title
FROM articles
WHERE content_tsv @@ phraseto_tsquery('english', 'high performance');
 
-- 权重标记（A、B、C、D）
SELECT setweight(to_tsvector('english', 'title'), 'A') ||
       setweight(to_tsvector('english', 'content'), 'B');
 
-- 带权重的排序
SELECT title,
       ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', content_tsv, query) AS rank
FROM articles,
     to_tsquery('english', 'database') AS query
WHERE content_tsv @@ query
ORDER BY rank DESC;
 
-- 删除已删除的文档
VACUUM FULL articles;

4.6 全文检索系统架构图

flowchart LR
    A[原始文本<br/>The quick brown fox] --> B[分词器<br/>Tokenizer]
    B --> C[词典处理<br/>Dictionary]
    C --> D[词位规范化<br/>Normalization]
    D --> E[tsvector<br/>'quick':2 'brown':3]

    F[搜索查询<br/>quick brown] --> G[查询解析<br/>Parser]
    G --> H[词位转换<br/>Lexemize]
    H --> I[tsquery<br/>'quick' & 'brown']

    E --> J[全文检索引擎]
    I --> J

    J --> K[GIN索引<br/>加速搜索]
    K --> L[搜索结果<br/>+相关性评分]

    M[配置选项] --> N[语言配置<br/>english/chinese]
    M --> O[词典配置<br/>stemming/synonym]
    M --> P[权重配置<br/>A/B/C/D]

    N --> C
    O --> C
    P --> L

图表说明：此架构图展示了PostgreSQL全文检索系统的完整处理流程。左侧展示文档如何转换为tsvector：经过分词、词典处理和规范化后生成词位向量。中间展示查询如何转换为tsquery：解析和词位转换后生成查询对象。两部分在全文检索引擎中匹配，利用GIN索引加速搜索，最终返回结果和相关性评分。底部展示了配置选项如何影响处理过程，包括语言配置、词典配置和权重配置。这张图帮助理解全文检索的内部机制和可配置性。

五、枚举类型

5.1 枚举类型概述

枚举类型（Enum Types）是一种包含一组静态值列表的数据类型。枚举类型在PostgreSQL中通过CREATE TYPE命令创建，非常适合存储状态、类型、分类等取值有限且固定的字段。

使用枚举类型的优势包括：

数据完整性保证：只能使用预定义的值
存储效率高：内部使用4字节存储
查询性能好：比字符串比较更快
代码可读性：值具有明确的语义

5.2 枚举类型的创建与使用

-- 创建枚举类型
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
CREATE TYPE user_role AS ENUM ('guest', 'user', 'moderator', 'admin');
CREATE TYPE priority_level AS ENUM ('low', 'medium', 'high', 'critical');
 
-- 创建使用枚举类型的表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    status order_status DEFAULT 'pending',
    priority priority_level DEFAULT 'medium',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 插入数据
INSERT INTO orders (customer_id, total_amount, status, priority)
VALUES (1001, 299.99, 'pending', 'high');
 
-- 查询数据
SELECT id, status, priority
FROM orders
WHERE status = 'pending'
ORDER BY priority DESC;
 
-- 更新状态
UPDATE orders
SET status = 'processing'
WHERE id = 1 AND status = 'pending';  -- 确保状态流转合法

5.3 枚举类型操作

-- 查看枚举类型的所有值
SELECT enumlabel FROM pg_enum
WHERE enumtypid = 'order_status'::regtype
ORDER BY enumsortorder;
 
-- 在枚举类型中添加新值（只能在末尾添加）
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';
 
-- 枚举类型比较
SELECT o1.id, o1.status
FROM orders o1
JOIN orders o2 ON o1.status < o2.status  -- 可以比较枚举值
WHERE o1.id = 1;
 
-- 获取枚举值的排序位置
SELECT id, status,
       status::text AS status_text,
       enum_first(null::order_status) AS first_status,
       enum_last(null::order_status) AS last_status
FROM orders;
 
-- 检查枚举值是否存在
SELECT 'refunded'::order_status IS NOT NULL;  -- 如果值存在返回true

5.4 枚举类型注意事项

使用枚举类型时需要注意以下几点：

修改枚举类型困难：一旦创建，修改枚举类型（删除或重排序值）非常复杂，需要重建表。
标签长度限制：枚举标签名称不能超过63个字符。
比较顺序：枚举值的比较顺序基于创建时的顺序，不是字母顺序。
性能考虑：对于小规模固定值集，枚举类型效率高；对于可能频繁变化的值集，考虑使用检查约束或外键关联表。

-- 对于可能需要修改的状态系统，可以考虑外键方案
CREATE TABLE order_statuses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    sort_order INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status_id INTEGER REFERENCES order_statuses(id),
    -- 其他字段
);

六、复合类型

6.1 复合类型概述

复合类型（Composite Types）允许将多个字段组合成一个单一的数据类型，类似于C语言的结构体或面向对象语言中的类。复合类型可以作为表的列类型，也可以作为函数的参数或返回值类型。

6.2 复合类型的创建与使用

-- 创建复合类型
CREATE TYPE address_type AS (
    street VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(50) DEFAULT 'China'
);
 
CREATE TYPE contact_info AS (
    email VARCHAR(100),
    phone VARCHAR(20),
    wechat VARCHAR(50)
);
 
-- 创建使用复合类型的表
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address address_type,
    contacts contact_info[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 插入数据（使用ROW构造函数）
INSERT INTO customers (name, address, contacts)
VALUES (
    '张三',
    ROW('中关村大街1号', '北京', '北京市', '100086', 'China'),
    ARRAY[
        ROW('[email protected]', '13800138000', 'zhangsan_wx')::contact_info,
        ROW('[email protected]', '13900139000', 'zhangsan_work')::contact_info
    ]
);
 
-- 查询复合类型字段
SELECT name,
       (address).city,
       (address).postal_code,
       contacts[1].email
FROM customers;
 
-- 使用点号访问复合类型字段
SELECT name,
       c.address.city,
       c.address.country,
       c.contacts[1].phone
FROM customers c;

6.3 复合类型与表的关系

PostgreSQL中的每个表都自动创建一个对应的复合类型，这使得表行可以作为复合类型使用。

-- 表自动创建对应的复合类型
CREATE TABLE products_temp (
    id INTEGER,
    name VARCHAR(100),
    price NUMERIC(10,2)
);
 
-- 可以使用表的复合类型
CREATE TABLE orders_items (
    order_id INTEGER,
    product products_temp,  -- 使用products_temp表的复合类型
    quantity INTEGER DEFAULT 1
);
 
-- 插入数据
INSERT INTO orders_items (order_id, product, quantity)
VALUES (1, ROW(101, '商品A', 99.99), 2);
 
-- 查询
SELECT (product).name, (product).price, quantity
FROM orders_items;

七、UUID与其他特殊类型

7.1 UUID类型

UUID（Universally Unique Identifier）是128位的全局唯一标识符，通常以36字符的字符串形式表示（如”550e8400-e29b-41d4-a716-446655440000”）。PostgreSQL通过uuid-ossp扩展或pgcrypto扩展提供UUID生成功能。

-- 启用UUID扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
-- 创建使用UUID的表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- 插入数据（自动生成UUID）
INSERT INTO users (username, email)
VALUES ('user1', '[email protected]');
 
-- 使用UUID作为外键
CREATE TABLE user_posts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- UUID函数
SELECT uuid_generate_v1();      -- 基于MAC地址和时间
SELECT uuid_generate_v4();      -- 随机生成
SELECT uuid_generate_v5(uuid_ns_dns(), 'example.com');  -- 基于命名空间

7.2 其他特殊数据类型

-- 网络地址类型
CREATE TABLE network_info (
    id SERIAL PRIMARY KEY,
    inet_addr INET,           -- IP地址和子网
    cidr_block CIDR,          -- 网络块
    mac_addr MACADDR          -- MAC地址
);
 
INSERT INTO network_info (inet_addr, cidr_block, mac_addr)
VALUES ('192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03');
 
-- IP地址运算
SELECT inet_addr,
       inet_addr << '192.168.1.0/24' AS is_in_subnet  -- 包含判断
FROM network_info;
 
-- 位串类型
CREATE TABLE permissions (
    user_id INTEGER PRIMARY KEY,
    flags BIT(8),             -- 定长位串
    permissions VARBIT,       -- 变长位串
    access_log BIT(64)
);
 
INSERT INTO permissions (user_id, flags, permissions)
VALUES (1, B'10110010', B'1101');
 
-- 位操作
SELECT user_id,
       flags & B'00001111' AS lower_flags,
       flags | B'10000000' AS set_flag,
       ~flags AS inverted
FROM permissions;
 
-- 货币类型
CREATE TABLE products_price (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price_cny MONEY,          -- 人民币
    price_usd MONEY           -- 美元
);
 
-- 设置货币格式
SET lc_monetary = 'zh_CN.UTF-8';
 
INSERT INTO products_price (name, price_cny, price_usd)
VALUES ('商品A', '¥299.99', '$49.99');
 
-- 对象标识符类型
CREATE TABLE system_objects (
    id OID PRIMARY KEY,
    object_name VARCHAR(100),
    regclass REGCLASS,        -- 类名
    regtype REGTYPE,          -- 类型名
    regproc REGPROC           -- 函数名
);
 
-- pg_identifyobject()函数示例
SELECT oid, typname
FROM pg_type
WHERE typname = 'int4';

八、数据类型知识总结

数据类型选择对照表

应用场景	推荐数据类型	备选方案	选择依据
标签/关键词	TEXT[] + GIN索引	关联表	查询频率vs规范化需求
用户配置	JSONB	JSON + 单独字段	查询需求vs格式保留
API响应	JSON	JSONB	原始存储需求
时间区间	DATERANGE/TSTZRANGE	两个时间戳字段	区间查询需求
价格区间	NUMRANGE + GiST索引	两个价格字段	范围操作需求
文章搜索	TSVECTOR + GIN索引	LIKE	搜索性能需求
订单状态	ENUM	外键关联表	稳定性vs灵活性
地址信息	复合类型	拆分字段	使用频率vs规范化
用户ID	UUID	SERIAL	分布式系统需求
二进制数据	BYTEA	LO	大小vs功能需求

九、常见问题解答

Q1：数组和关联表如何选择？

答：数组适合值数量相对固定、不需要频繁修改、查询相对简单的场景，如商品标签、用户兴趣等。

关联表适合需要频繁修改、需要复杂查询和统计、需要保证数据引用完整性的场景。

考虑因素包括：数据量（数组适合少量值）、查询模式（数组适合简单包含查询）、更新频率（数组适合低频更新）、数据完整性需求（关联表更适合外键约束）。

Q2：JSON和JSONB如何选择？

答：选择JSON的情况：需要保留原始JSON格式（如精确的空格和键顺序）、插入速度比查询速度更重要、很少需要查询JSON内部字段。

选择JSONB的情况：需要频繁查询JSON内部字段、需要创建索引、需要执行JSON修改操作。

大多数应用场景下JSONB是更好的选择。

Q3：全文检索和LIKE有什么区别？

答：LIKE进行简单的模式匹配，适合精确的前缀或后缀搜索。

但不支持词干提取、相关性排序等高级功能，性能随数据量增长快速下降。

全文检索通过分词、词干提取、建立索引等技术，支持智能搜索、相关性排序、短语搜索等高级功能，在大数据量下性能稳定。对于需要智能搜索功能的文本数据，应优先选择全文检索。

Q4：UUID作为主键有什么优缺点？

答：优点：全局唯一性适合分布式系统、不暴露业务信息、可以在客户端生成。

缺点：占用16字节存储空间大于SERIAL的4字节、无序性导致B-Tree索引插入性能较差、可读性差。

对于集中式应用，SERIAL或BIGSERIAL通常更合适。对于分布式系统或需要全局唯一标识的场景，UUID是合理选择。可以考虑使用UUID v7等时间排序的UUID变体来改善索引性能。

Q5：如何为JSONB字段创建合适的索引？

答：根据查询模式选择：对于包含查询（@>操作符），使用默认GIN索引。

对于仅包含查询且不需要其他JSON操作符，使用jsonb_path_ops操作符类的GIN索引（索引更小）。

对于特定字段的相等查询，创建表达式索引（如(jsonb→>‘field’)）；对于嵌套字段，使用路径表达式创建索引。索引选择应基于实际的查询模式，避免创建过多索引。

十、总结与下一篇预告

本文系统讲解了PostgreSQL的特色数据类型和高级功能，包括数组、JSON/JSONB、范围类型、全文检索、枚举、复合类型以及UUID等特殊类型。这些数据类型大大增强了PostgreSQL处理复杂数据的能力，使得许多传统上需要应用层处理的数据操作可以在数据库层面高效完成。

掌握这些数据类型的使用方法和适用场景，能够帮助开发者在实际项目中设计更优雅、高效的数据库方案。数组类型简化了多值属性的存储，JSONB提供了灵活的半结构化数据处理能力，范围类型使区间查询变得简单，全文检索提供了强大的文本搜索功能。

下一篇预告：《PostgreSQL数据库技术第 6 篇：事务处理与并发控制》将深入探讨PostgreSQL的MVCC多版本并发控制机制、事务隔离级别、锁机制、死锁处理以及写前日志（WAL）等核心概念，帮助读者理解数据库如何在高并发环境下保证数据一致性和隔离性。

本文作为PostgreSQL数据库技术系列的第五篇，专注于特色数据类型和高级功能特性的讲解，为后续学习事务处理和数据库管理打下坚实基础。

51学通信

探索

PostgreSQL数据库技术 第 5 篇：数据类型与高级特性

PostgreSQL数据库技术 第 5 篇：数据类型与高级特性

摘要

学习目标

一、数组类型

1.1 数组类型概述

1.2 数组的创建与初始化

1.3 数组操作函数与运算符

1.4 数组索引策略

1.5 数组类型应用场景图解

1.6 数组类型的最佳实践

二、JSON与JSONB数据类型

2.1 JSON与JSONB的区别

2.2 JSON/JSONB数据操作

2.3 JSONB查询与操作

2.4 JSONB操作符与函数速查表

2.5 JSONB索引策略

2.6 JSON数据类型选择决策图

三、范围类型

3.1 范围类型概述

3.2 范围类型的创建与使用

3.3 范围查询与操作

3.4 范围类型操作符表

3.5 多范围类型

3.6 范围类型应用场景图解

四、全文检索

4.1 全文检索原理

4.2 全文检索数据类型

4.3 全文检索基本操作

4.4 文本搜索配置

4.5 全文检索高级功能

4.6 全文检索系统架构图

五、枚举类型

5.1 枚举类型概述

5.2 枚举类型的创建与使用

5.3 枚举类型操作

5.4 枚举类型注意事项

六、复合类型

6.1 复合类型概述

6.2 复合类型的创建与使用

6.3 复合类型与表的关系

七、UUID与其他特殊类型

7.1 UUID类型

7.2 其他特殊数据类型

八、数据类型知识总结

数据类型选择对照表

九、常见问题解答

Q1：数组和关联表如何选择？

Q2：JSON和JSONB如何选择？

Q3：全文检索和LIKE有什么区别？

Q4：UUID作为主键有什么优缺点？

Q5：如何为JSONB字段创建合适的索引？

十、总结与下一篇预告

关系图谱

目录

PostgreSQL数据库技术第 5 篇：数据类型与高级特性

PostgreSQL数据库技术第 5 篇：数据类型与高级特性