PostgreSQL 教程:边运行边学
免费的 PostgreSQL 教程,查询都能在页面上直接运行:数据类型、CREATE TABLE、upsert、JSONB、日期、CTE、窗口函数、索引和 VACUUM。修改查询,再运行一次就能看到结果。
开始课程入门
- PostgreSQL 和 MySQLPostgreSQL 和 MySQL 都是免费的关系型数据库。PostgreSQL 的 SQL 更丰富,有 JSONB 和支持事务的 DDL;在已经默认用 MySQL 的环境里,MySQL 运维更简单。
- PostgreSQL 和 SQL ServerPostgreSQL 免费开源;SQL Server 是微软的商业数据库,使用 T-SQL 和以 Windows 为中心的工具链。下面介绍两者在 SQL、JSON、锁和许可证上的区别。
- PostgreSQL 和 MongoDBPostgreSQL 把行存在结构固定的表中,也能在 jsonb 列里存放 JSON 文档;MongoDB 把结构灵活的文档存在集合中,并在多台服务器之间分片。
- 查看数据库和表在 psql 中,\l 列出数据库,\c 切换数据库,\dt 列出其中的表。在 SQL 中,可以查询 pg_database、information_schema.tables 和 information_schema.columns。
表与类型
- 数据类型几乎每一列都会用到的 PostgreSQL 数据类型:整数、numeric、text、boolean、timestamptz、uuid、jsonb、数组和枚举,以及各自适合的场景。
- CREATE TABLECREATE TABLE 定义表的列、类型和约束。内容包括标识列主键、NOT NULL、DEFAULT、UNIQUE、CHECK、IF NOT EXISTS、CREATE TABLE AS 和 LIKE。
- ALTER TABLEALTER TABLE 用来修改已有的表:添加、重命名和删除列,用 USING 修改列的类型,设置默认值和 NOT NULL,以及添加约束。
- 主键如何在 PostgreSQL 中定义主键,以及如何生成主键:标识列、较早的 SERIAL,以及用 gen_random_uuid() 生成 UUID。还包括复合主键和重置序列。
- 外键外键让一列只接受另一张表中存在的值。内容包括 REFERENCES、ON DELETE CASCADE、SET NULL 和 RESTRICT,给已有的表添加外键,以及为外键建索引。
- 枚举类型枚举类型是一组固定且有序的允许值,用 CREATE TYPE ... AS ENUM 创建。内容包括添加和重命名值、列出所有值、排序,以及什么时候 CHECK 约束更合适。
- 时间戳timestamptz 存储一个时间点,并按会话时区显示;timestamp 存储不带时区的挂钟时间。两者都不会保存你写入的时区。几乎所有场景都用 timestamptz。
查询
- 连接(JOIN)连接按条件把两张表的行组合在一起。INNER JOIN 保留匹配的行,LEFT JOIN 保留第一张表的每一行,FULL JOIN 两边都保留,CROSS JOIN 两两配对,LATERAL 为每一行运行一次子查询。
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort 保留每组的第一行,比如每位客户的最新订单。这是 PostgreSQL 独有的写法。
- COALESCE、NULLIF 和 CASECOALESCE(a, b, ...) 返回第一个不是 NULL 的参数,NULLIF(a, b) 把一个值变成 NULL,CASE WHEN 按条件选择一个值。
- LIKE、ILIKE 和正则LIKE 用 % 和 _ 按模式匹配文本,ILIKE 做同样的匹配但忽略大小写,~ 系列操作符匹配 POSIX 正则表达式。
- CAST 和 ::在 PostgreSQL 中,CAST(value AS type) 和 value::type 都能把值转换成另一种类型。两者是同一种转换,:: 是 PostgreSQL 更短的写法。
修改数据
JSON 与数组
字符串与日期
- 字符串函数日常最常用的 PostgreSQL 字符串函数:substring、left 和 right、|| 和 concat、split_part、position、replace、regexp_replace、trim、lpad 和 format。
- 日期函数在 PostgreSQL 中处理日期:now() 和 current_date,加减时间间隔,用 date_trunc 分组,extract 和 age,以及用 to_char 格式化。
- generate_seriesgenerate_series 返回从起始值到结束值的一组行:数字、日期和时间戳。用它补齐报表中的空缺、生成测试数据和构建日历。
高级查询
性能与维护
- 索引CREATE INDEX 默认创建 B-tree 索引,让 PostgreSQL 不必读整张表就能找到行。其他类型(GIN、GiST、BRIN、hash)适用于 JSONB、数组、全文搜索、范围类型和超大表。
- EXPLAIN ANALYZEEXPLAIN 显示 PostgreSQL 为查询选择的执行计划,EXPLAIN ANALYZE 会真正运行查询,并补上每一步的实际行数和耗时。学会阅读计划树和常见的节点名称。
- 视图和物化视图视图是一条保存下来的查询,每次读取时都会运行。物化视图存储查询的结果,只有运行 REFRESH MATERIALIZED VIEW 时才会变化。
- VACUUMVACUUM 清除 UPDATE 和 DELETE 留下的死行版本,让它们占用的空间可以重复使用。autovacuum 会自动替你运行它;本页介绍它何时运行、如何调优,以及什么时候值得为 VACUUM FULL 承受锁表。