PostgreSQL教程 - 最先进的开源关系型数据库

PostgreSQL是一款以稳定性、标准符合度和功能丰富著称的开源关系型数据库。它被誉为"世界上最先进的开源数据库"，支持复杂查询、JSON、地理空间数据、全文检索等高级功能。本教程将助你全面掌握PostgreSQL。

一、PostgreSQL简介

PostgreSQL起源于1986年加州大学伯克利分校的POSTGRES项目，于1996年正式更名为PostgreSQL。它严格遵循SQL标准，支持ACID事务，拥有极强的扩展性，许多商业数据库的功能都借鉴自PostgreSQL。

二、你将学到什么

• 基础操作：建库建表、数据类型、CRUD、约束
• 查询进阶：窗口函数、CTE递归查询、JSON操作
• 索引：B-Tree、GIN、GiST、BRIN、部分索引
• 高级功能：存储过程、触发器、视图、物化视图
• PostGIS：地理空间数据存储与查询
• 运维管理：备份恢复、流复制、性能调优

三、代码示例

PostgreSQL高级查询示例：

-- 创建带JSONB字段的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    attrs JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入数据
INSERT INTO products (name, attrs) VALUES
("笔记本电脑", "{"brand":"ThinkPad","cpu":"i7","ram":16}"),
("手机", "{"brand":"iPhone","storage":128,"os":"iOS"}");

-- JSONB查询：查找品牌为ThinkPad的商品
SELECT name, attrs->>"brand" AS brand
FROM products
WHERE attrs @> "{"brand":"ThinkPad"}";

-- 窗口函数：计算每个分类商品价格排名
SELECT
    name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank,
    AVG(price) OVER (PARTITION BY category) AS avg_price
FROM products;

-- CTE递归查询：组织架构树
WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM departments WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id, ot.level + 1
    FROM departments d
    JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level;

四、适合人群

• 后端开发者，想要使用更强大的数据库
• 数据工程师，需要处理复杂的数据查询
• GIS开发者，需要使用PostGIS处理地理数据
• 从MySQL转向PostgreSQL的开发者

五、学习建议

PostgreSQL与MySQL语法相似但功能更丰富。建议先复习标准SQL，再学习PostgreSQL特有的JSONB操作和窗口函数。重点理解MVCC并发控制和索引类型选择。如果从事GIS开发，PostGIS是必学内容。多阅读官方文档，它的文档质量在开源项目中名列前茅。

课程信息：
共 4 个章节