当前位置：首页 > news >正文

PostgreSQL高级特性详解

news 2026/5/10 22:53:38

PostgreSQL高级特性详解

1. PostgreSQL概述

PostgreSQL是功能强大的开源对象关系型数据库，支持SQL标准扩展、MVCC、触发器、存储过程等高级特性。

2. 高级数据类型

2.1 JSON/JSONB

-- 创建JSONB列 CREATE TABLE events ( id SERIAL PRIMARY KEY, data JSONB ); -- 插入JSON数据 INSERT INTO events (data) VALUES ('{"name": "John", "age": 30}'), ('{"name": "Jane", "tags": ["a", "b"]}'); -- 查询JSON字段 SELECT>-- 创建数组列 CREATE TABLE products ( id SERIAL PRIMARY KEY, tags TEXT[] ); -- 插入数组 INSERT INTO products (tags) VALUES ('{"electronics", "sale"}'), ('{"clothing", "new"}'); -- 查询数组包含 SELECT * FROM products WHERE tags @> ARRAY['electronics']; -- 数组操作 SELECT * FROM products WHERE 'sale' = ANY(tags);

2.3 范围类型

-- 创建时间范围列 CREATE TABLE reservations ( id SERIAL PRIMARY KEY, room INT, period TSRANGE ); -- 插入时间范围 INSERT INTO reservations (room, period) VALUES (101, '[2023-01-01, 2023-01-05)'), (102, '[2023-01-03, 2023-01-07)'); -- 查询重叠 SELECT * FROM reservations WHERE period && '[2023-01-02, 2023-01-04)'::TSRANGE;

3. 高级索引

3.1 部分索引

-- 只对活跃用户的索引 CREATE INDEX idx_users_active ON users (email) WHERE status = 'active'; -- 查询只会使用索引 SELECT * FROM users WHERE status = 'active' AND email = 'john@example.com';

3.2 表达式索引

-- 对函数结果建索引 CREATE INDEX idx_users_lower ON users (LOWER(email)); -- 查询使用索引 SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

3.3 GiST索引

用于几何和范围类型：

CREATE INDEX idx_locations ON locations USING GIST (coordinates);

4. 分区表

4.1 范围分区

-- 创建分区表 CREATE TABLE orders ( id SERIAL, created_at DATE, amount DECIMAL(10,2) ) PARTITION BY RANGE (created_at); -- 创建分区 CREATE TABLE orders_2023_01 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2023-02-01'); CREATE TABLE orders_2023_02 PARTITION OF orders FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'); -- 查询自动使用分区 SELECT * FROM orders WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31';

4.2 列表分区

CREATE TABLE products ( id SERIAL, category TEXT, name TEXT ) PARTITION BY LIST (category); CREATE TABLE products_electronics PARTITION OF products FOR VALUES IN ('electronics'); CREATE TABLE products_clothing PARTITION OF products FOR VALUES IN ('clothing');

5. 继承与表继承

-- 创建父表 CREATE TABLE cities ( name TEXT, population INT, elevation INT ); -- 创建子表 CREATE TABLE capitals ( state TEXT ) INHERITS (cities); -- 查询包含继承 SELECT name, population FROM cities; -- capitals表的数据也会被返回

6. 窗口函数

6.1 排名函数

SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num FROM employees;

6.2 聚合窗口函数

SELECT name, department, salary, SUM(salary) OVER (PARTITION BY department) as dept_total, AVG(salary) OVER (PARTITION BY department) as dept_avg, salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg FROM employees;

6.3 前后值

SELECT created_at, value, LAG(value) OVER (ORDER BY created_at) as prev_value, LEAD(value) OVER (ORDER BY created_at) as next_value FROM measurements;

7. CTE公用表表达式

7.1 递归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, ot.level + 1 FROM employees e JOIN org_tree ot ON e.manager_id = ot.id ) SELECT * FROM org_tree ORDER BY level, name;

7.2 多个CTE

WITH active_users AS ( SELECT COUNT(*) as count FROM users WHERE status = 'active' ), total_orders AS ( SELECT COUNT(*) as count FROM orders ), avg_order_value AS ( SELECT AVG(amount) as avg FROM orders ) SELECT * FROM active_users, total_orders, avg_order_value;

8. 触发器

8.1 创建触发器函数

CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

8.2 创建触发器

CREATE TRIGGER trigger_users_updated BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at(); -- 审计触发器 CREATE OR REPLACE FUNCTION audit_log() RETURNS TRIGGER AS $$ BEGIN INSERT INTO audit_table (action, table_name, old_data, new_data) VALUES (TG_OP, TG_TABLE_NAME, OLD, NEW); RETURN NEW; END; $$ LANGUAGE plpgsql;

9. 存储过程

CREATE OR REPLACE FUNCTION transfer_funds( from_account INT, to_account INT, amount DECIMAL ) RETURNS VOID AS $$ BEGIN -- 检查余额 IF (SELECT balance FROM accounts WHERE id = from_account) < amount THEN RAISE EXCEPTION 'Insufficient funds'; END IF; -- 执行转账 UPDATE accounts SET balance = balance - amount WHERE id = from_account; UPDATE accounts SET balance = balance + amount WHERE id = to_account; END; $$ LANGUAGE plpgsql; -- 调用 SELECT transfer_funds(1, 2, 100.00);

10. 物化视图

-- 创建物化视图 CREATE MATERIALIZED VIEW sales_summary AS SELECT date_trunc('month', created_at) as month, SUM(amount) as total_sales, COUNT(*) as order_count FROM orders GROUP BY date_trunc('month', created_at) WITH DATA; -- 创建索引 CREATE UNIQUE INDEX ON sales_summary (month); -- 刷新物化视图 REFRESH MATERIALIZED VIEW sales_summary;