PostgreSQL 高级功能与扩展（九）

1. JSONB 数据类型与操作

1.1 JSONB 简介

JSONB 是 PostgreSQL 中的一种数据类型，用于存储 JSON 格式的数据，并提供高效的查询和索引功能。

1.1.1 创建 JSONB 列

CREATE TABLE json_data ( id SERIAL PRIMARY KEY, data JSONB );

1.2 JSONB 查询与索引

1.2.1 JSONB 查询操作

-- 查询包含特定键值对的行
SELECT * FROM json_data WHERE data @> '{"key": "value"}';

-- 查询包含特定键的行
SELECT * FROM json_data WHERE data ? 'key';

-- 查询数组元素
SELECT * FROM json_data WHERE data -> 'array_key' @> '["element1", "element2"]';

-- 展开 JSONB 对象
SELECT id, (data -> 'nested_key1' ->> 'nested_key2') AS nested_value FROM json_data;

1.2.2 创建 JSONB 索引

CREATE INDEX idx_jsonb_key ON json_data USING GIN (data jsonb_path_ops);

1.3 JSONB 函数

1.3.1 JSONB 函数示例

-- 更新 JSONB 字段
UPDATE json_data SET data = jsonb_set(data, '{key}', '"new_value"', true);

-- 删除 JSONB 字段
UPDATE json_data SET data = data - 'key';

-- 合并 JSONB 对象
SELECT jsonb_concat(data1, data2) AS merged_data FROM json_data;

2. 全文搜索与搜索引擎集成

2.1 全文搜索功能

PostgreSQL 提供了全文搜索功能，允许在文本数据上执行复杂的搜索操作。

2.1.1 配置全文搜索

CREATE TEXT SEARCH DICTIONARY english_ispell (
    TEMPLATE = ispell,
    DictFile = english,
    AffFile = english,
    StopWords = english
);

ALTER TEXT SEARCH CONFIGURATION english ALTER MAPPING FOR asciiword WITH english_ispell;

2.2 全文搜索操作

2.2.1 全文搜索查询

SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'search_query');

2.3 全文搜索索引

2.3.1 创建全文搜索索引

CREATE INDEX idx_full_text_search ON documents USING GIN (to_tsvector('english', content));

2.4 全文搜索与外部搜索引擎集成

2.4.1 使用 pg_search 扩展

CREATE EXTENSION pg_trgm;
CREATE EXTENSION pgcrypto;

-- Query:
SELECT query, total_time
FROM pg_stat_statements
ORDER BY total_time

---

系统文章目录：

PostgreSQL 简介与基础（一）

PostgreSQL 基本SQL语法（二）

PostgreSQL 高级SQL查询（三）

PostgreSQL 数据库设计与管理（四）

PostgreSQL 高级功能（五）

PostgreSQL 性能优化与调优（六）

PostgreSQL 高可用性与灾难恢复策略（七）

PostgreSQL 安全性与权限管理（八）

PostgreSQL 高级功能与扩展（九）

PostgreSQL 分区表与并行查询（十）

PostgreSQL 索引优化与性能调优（十一）

PostgreSQL 日志管理与故障排查（十二）

PostgreSQL 高可用性与容错性（十三）