PostgreSQL高级特性详解

PostgreSQL高级特性详解

PostgreSQL是最先进的开源关系型数据库，拥有许多MySQL不具备的高级特性。本文将介绍PostgreSQL的一些独特功能，帮助你更好地利用这个强大的数据库。

一、JSONB数据类型

-- 创建带JSONB列的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    attributes JSONB
);

-- 插入JSON数据
INSERT INTO products (name, attributes) VALUES
("笔记本电脑", "{"brand": "联想", "cpu": "i7", "ram": 16, "ssd": true}");

-- JSONB查询
SELECT * FROM products WHERE attributes->>"brand" = "联想";
SELECT * FROM WHERE attributes->"ram" > 8;

-- JSONB包含查询
SELECT * FROM products WHERE attributes @> "{"ssd": true}";

-- JSONB索引
CREATE INDEX idx_attrs ON products USING GIN (attributes);
CREATE INDEX idx_attrs_brand ON products ((attributes->>"brand"));

二、窗口函数

-- 排名函数
SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) as overall_rank,
    PERCENT_RANK() OVER (ORDER BY salary) as percentile
FROM employees;

-- 累计计算
SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as running_total,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_7d
FROM daily_sales;

-- 前后对比
SELECT 
    date,
    amount,
    LAG(amount, 1) OVER (ORDER BY date) as prev_day,
    LEAD(amount, 1) OVER (ORDER BY date) as next_day,
    amount - LAG(amount, 1) OVER (ORDER BY date) as daily_change
FROM daily_sales;

三、CTE与递归查询

-- 递归CTE - 组织架构树
WITH RECURSIVE org_tree AS (
    -- 基础查询：顶级管理者
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：下属
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level;

-- 物化CTE - 性能优化
WITH MATERIALIZED expensive_query AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
)
SELECT * FROM expensive_query WHERE order_count > 10;

四、全文搜索

-- 创建全文搜索索引
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector = 
    to_tsvector("simple", coalesce(title, "") || " " || coalesce(content, ""));

CREATE INDEX idx_search ON articles USING GIN (search_vector);

-- 全文搜索查询
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery("simple", "数据库 & 优化") query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 自动更新搜索向量
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(search_vector, "simple", title, content);

PostgreSQL的丰富特性使它不仅是一个关系型数据库，更是一个强大的数据平台。合理利用JSONB、窗口函数、递归查询和全文搜索等特性，可以大幅减少应用层的复杂度，让数据库承担更多数据处理工作。