很楠不爱3

这个屌丝很懒，什么也没留下！

热门标签

PostgreSQL 12.2 手册_postgre 手册

作者：很楠不爱3 | 2024-04-11 16:22:15

踩

postgre 手册

目录
前言

何为PostgreSQL？
PostgreSQL简史
2.1. 伯克利的POSTGRES项目
2.2. Postgres95
2.3. PostgreSQL
约定
进一步的信息
缺陷报告指南
5.1. 标识缺陷
5.2. 报告什么
5.3. 向哪里报告缺陷
I. 教程
从头开始
1.1. 安装
1.2. 架构基础
1.3. 创建一个数据库
1.4. 访问数据库
SQL语言
2.1. 引言
2.2. 概念
2.3. 创建一个新表
2.4. 在表中增加行
2.5. 查询一个表
2.6. 在表之间连接
2.7. 聚集函数
2.8. 更新
2.9. 删除
高级特性
3.1. 简介
3.2. 视图
3.3. 外键
3.4. 事务
3.5. 窗口函数
3.6. 继承
3.7. 小结
II. SQL 语言
SQL语法
4.1. 词法结构
4.2. 值表达式
4.3. 调用函数
数据定义
5.1. 表基础
5.2. 默认值
5.3. 生成列
5.4. 约束
5.5. 系统列
5.6. 修改表
5.7. 权限
5.8. 行安全性策略
5.9. 模式
5.10. 继承
5.11. 表分区
5.12. 外部数据
5.13. 其他数据库对象
iii
PostgreSQL 12.2 手册
5.14. 依赖跟踪
数据操纵
6.1. 插入数据
6.2. 更新数据
6.3. 删除数据
6.4. 从修改的行中返回数据
查询
7.1. 概述
7.2. 表表达式
7.3. 选择列表
7.4. 组合查询
7.5. 行排序
7.6. LIMIT和OFFSET
7.7. VALUES列表
7.8. WITH查询（公共表表达式）
数据类型
8.1. 数字类型
8.2. 货币类型
8.3. 字符类型
8.4. 二进制数据类型
8.5. 日期/时间类型
8.6. 布尔类型
8.7. 枚举类型
8.8. 几何类型
8.9. 网络地址类型
8.10. 位串类型
8.11. 文本搜索类型
8.12. UUID类型
8.13. XML类型
8.14. JSON 类型
8.15. 数组
8.16. 组合类型
8.17. 范围类型
8.18. 域类型
8.19. 对象标识符类型
8.20. pg_lsn 类型
8.21. 伪类型
函数和操作符
9.1. 逻辑操作符
9.2. 比较函数和操作符
9.3. 数学函数和操作符
9.4. 字符串函数和操作符 … 197
9.5. 二进制串函数和操作符 … 209
9.6. 位串函数和操作符 … 211
9.7. 模式匹配 … 212
9.8. 数据类型格式化函数 … 227
9.9. 时间/日期函数和操作符 … 234
9.10. 枚举支持函数 … 247
9.11. 几何函数和操作符 … 247
9.12. 网络地址函数和操作符 … 251
9.13. 文本搜索函数和操作符 … 253
9.14. XML 函数 … 259
9.15. JSON 函数和操作符 … 271
9.16. 序列操作函数 … 285
9.17. 条件表达式 … 288
9.18. 数组函数和操作符 … 290
9.19. 范围函数和操作符 … 293
9.20. 聚集函数 … 294
iv
PostgreSQL 12.2 手册
9.21. 窗口函数 … 300
9.22. 子查询表达式 … 302
9.23. 行和数组比较 … 304
9.24. 集合返回函数 … 307
9.25. 系统信息函数和运算符 … 310
9.26. 系统管理函数 … 325
9.27. 触发器函数 … 341
9.28. 事件触发器函数 … 342
9.29. Statistics Information Functions … 344
类型转换 … 345
10.1. 概述 … 345
10.2. 操作符 … 346
10.3. 函数 … 349
10.4. 值存储 … 353
10.5. UNION、CASE和相关结构 … 354
10.6. SELECT的输出列 … 355
索引 … 357
11.1. 简介 … 357
11.2. 索引类型 … 358
11.3. 多列索引 … 359
11.4. 索引和ORDER BY … 360
11.5. 组合多个索引 … 361
11.6. 唯一索引 … 362
11.7. 表达式索引 … 362
11.8. 部分索引 … 363
11.9. 只用索引的扫描和覆盖索引 … 365
11.10. 操作符类和操作符族 … 367
11.11. 索引和排序规则 … 368
11.12. 检查索引使用 … 369
全文搜索 … 371
12.1. 介绍 … 371
12.2. 表和索引 … 374
12.3. 空值文本搜索 … 376
12.4. 额外特性 … 382
12.5. 解析器 … 387
12.6. 词典 … 389
12.7. 配置例子 … 398
12.8. 测试和调试文本搜索 … 399
12.9. GIN 和 GiST 索引类型 … 403
12.10. psql支持 … 404
12.11. 限制 … 406
并发控制 … 408
13.1. 介绍 … 408
13.2. 事务隔离 … 408
13.3. 显式锁定 … 413
13.4. 应用级别的数据完整性检查 … 417
13.5. 提醒 … 419
13.6. 锁定和索引 … 419
性能提示 … 420
14.1. 使用EXPLAIN … 420
14.2. 规划器使用的统计信息 … 430
14.3. 用显式JOIN子句控制规划器 … 434
14.4. 填充一个数据库 … 436
14.5. 非持久设置 … 438
并行查询 … 439
15.1. 并行查询如何工作 … 439
15.2. 何时会用到并行查询？ … 440
15.3. 并行计划 … 440
v
PostgreSQL 12.2 手册
15.4. 并行安全性 … 442
III. 服务器管理 … 444
从源代码安装 … 450
16.1. 简单版 … 450
16.2. 要求 … 450
16.3. 获取源码 … 451
16.4. 安装过程 … 452
16.5. 安装后设置 … 463
16.6. 平台支持 … 464
16.7. 平台相关的说明 … 465
在Windows上从源代码安装 … 469
17.1. 使用Visual C++或Microsoft Windows SDK构建 … 469
服务器设置和操作 … 474
18.1. PostgreSQL用户账户 … 474
18.2. 创建一个数据库集簇 … 474
18.3. 启动数据库服务器 … 476
18.4. 管理内核资源 … 479
18.5. 关闭服务器 … 487
18.6. 升级一个PostgreSQL集簇 … 488
18.7. 阻止服务器欺骗 … 490
18.8. 加密选项 … 491
18.9. 用 SSL 进行安全的 TCP/IP 连接 … 492
18.10. Secure TCP/IP Connections with GSSAPI Encryption … 495
18.11. 使用SSH隧道的安全 TCP/IP 连接 … 496
18.12. 在Windows上注册事件日志 … 496
服务器配置 … 498
19.1. 设置参数 … 498
19.2. 文件位置 … 501
19.3. 连接和认证 … 502
19.4. 资源消耗 … 508
19.5. 预写式日志 … 514
19.6. 复制 … 521
19.7. 查询规划 … 526
19.8. 错误报告和日志 … 533
19.9. 运行时统计数据 … 542
19.10. 自动清理 … 543
19.11. 客户端连接默认值 … 545
19.12. 锁管理 … 553
19.13. 版本和平台兼容性 … 554
19.14. 错误处理 … 555
19.15. 预置选项 … 556
19.16. 自定义选项 … 557
19.17. 开发者选项 … 557
19.18. 短选项 … 560
客户端认证 … 562
20.1. pg_hba.conf文件 … 562
20.2. 用户名映射 … 568
20.3. 认证方法 … 569
20.4. 信任认证 … 570
20.5. 口令认证 … 570
20.6. GSSAPI 认证 … 571
20.7. SSPI 认证 … 572
20.8. Ident 认证 … 573
20.9. Peer 认证 … 574
20.10. LDAP 认证 … 574
20.11. RADIUS 认证 … 577
20.12. 证书认证 … 577
20.13. PAM 认证 … 578
vi
PostgreSQL 12.2 手册
20.14. BSD 认证 … 578
20.15. 认证问题 … 579
数据库角色 … 580
21.1. 数据库角色 … 580
21.2. 角色属性 … 581
21.3. 角色成员关系 … 582
21.4. 删除角色 … 583
21.5. 默认角色 … 584
21.6. 函数和触发器安全性 … 585
管理数据库 … 586
22.1. 概述 … 586
22.2. 创建一个数据库 … 586
22.3. 模板数据库 … 587
22.4. 数据库配置 … 588
22.5. 销毁一个数据库 … 588
22.6. 表空间 … 589
本地化 … 591
23.1. 区域支持 … 591
23.2. 排序规则支持 … 593
23.3. 字符集支持 … 598
日常数据库维护工作 … 605
24.1. 日常清理 … 605
24.2. 日常重建索引 … 611
24.3. 日志文件维护 … 612
备份和恢复 … 614
25.1. SQL转储 … 614
25.2. 文件系统级别备份 … 617
25.3. 连续归档和时间点恢复（PITR） … 617
高可用、负载均衡和复制 … 628
26.1. 不同方案的比较 … 628
26.2. 日志传送后备服务器 … 631
26.3. 故障转移 … 638
26.4. 日志传送的替代方法 … 639
26.5. 热备 … 640
监控数据库活动 … 647
27.1. 标准 Unix 工具 … 647
27.2. 统计收集器 … 648
27.3. 查看锁 … 677
27.4. 进度报告 … 677
27.5. 动态追踪 … 682
监控磁盘使用 … 691
28.1. 判断磁盘用量 … 691
28.2. 磁盘满失败 … 692
可靠性和预写式日志 … 693
29.1. 可靠性 … 693
29.2. 预写式日志（WAL） … 694
29.3. 异步提交 … 695
29.4. WAL配置 … 696
29.5. WAL内部 … 698
逻辑复制 … 700
30.1. 发布 … 700
30.2. 订阅 … 701
30.3. 冲突 … 702
30.4. 限制 … 702
30.5. 架构 … 702
30.6. 监控 … 703
30.7. 安全性 … 703
30.8. 配置设置 … 703
vii
PostgreSQL 12.2 手册
30.9. 快速设置 … 704
即时编译（JIT） … 705
31.1. 什么是JIT编译？ … 705
31.2. 什么时候会用JIT？ … 705
31.3. 配置 … 706
31.4. 可扩展性 … 707
回归测试 … 708
32.1. 运行测试 … 708
32.2. 测试评估 … 711
32.3. 变体比较文件 … 713
32.4. TAP 测试 … 714
32.5. 测试覆盖检查 … 715
IV. 客户端接口 … 716
libpq - C 库 … 721
33.1. 数据库连接控制函数 … 721
33.2. 连接状态函数 … 733
33.3. 命令执行函数 … 739
33.4. 异步命令处理 … 753
33.5. 一行一行地检索查询结果 … 756
33.6. 取消进行中的查询 … 757
33.7. 快速路径接口 … 758
33.8. 异步提示 … 759
33.9. COPY命令相关的函数 … 760
33.10. 控制函数 … 764
33.11. 杂项函数 … 765
33.12. 通知处理 … 768
33.13. 事件系统 … 769
33.14. 环境变量 … 775
33.15. 口令文件 … 776
33.16. 连接服务文件 … 777
33.17. 连接参数的 LDAP 查找 … 777
33.18. SSL 支持 … 778
33.19. 在线程化程序中的行为 … 782
33.20. 编译 libpq 程序 … 782
33.21. 例子程序 … 784
大对象 … 794
34.1. 简介 … 794
34.2. 实现特性 … 794
34.3. 客户端接口 … 794
34.4. 服务器端函数 … 798
34.5. 例子程序 … 799
ECPG - C 中的嵌入式 SQL … 805
35.1. 概念 … 805
35.2. 管理数据库连接 … 805
35.3. 运行 SQL 命令 … 808
35.4. 使用主变量 … 811
35.5. 动态 SQL … 824
35.6. pgtypes 库 … 826
35.7. 使用描述符区域 … 839
35.8. 错误处理 … 852
35.9. 预处理器指令 … 858
35.10. 处理嵌入式 SQL 程序 … 860
35.11. 库函数 … 861
35.12. 大对象 … 862
35.13. C++ 应用 … 863
35.14. 嵌入式 SQL 命令 … 867
35.15. Informix兼容模式 … 889
35.16. 内部 … 903
viii
PostgreSQL 12.2 手册
信息模式 … 906
36.1. 模式 … 906
36.2. 数据类型 … 906
36.3. information_schema_catalog_name … 907
36.4. administrable_role_authorizations … 907
36.5. applicable_roles … 907
36.6. attributes … 908
36.7. character_sets … 910
36.8. check_constraint_routine_usage … 911
36.9. check_constraints … 911
36.10. collations … 912
36.11. collation_character_set_applicability … 912
36.12. column_column_usage … 912
36.13. column_domain_usage … 913
36.14. column_options … 913
36.15. column_privileges … 913
36.16. column_udt_usage … 914
36.17. columns … 914
36.18. constraint_column_usage … 918
36.19. constraint_table_usage … 918
36.20. data_type_privileges … 919
36.21. domain_constraints … 919
36.22. domain_udt_usage … 920
36.23. domains … 920
36.24. element_types … 923
36.25. enabled_roles … 925
36.26. foreign_data_wrapper_options … 925
36.27. foreign_data_wrappers … 925
36.28. foreign_server_options … 926
36.29. foreign_servers … 926
36.30. foreign_table_options … 926
36.31. foreign_tables … 927
36.32. key_column_usage … 927
36.33. parameters … 928
36.34. referential_constraints … 930
36.35. role_column_grants … 930
36.36. role_routine_grants … 931
36.37. role_table_grants … 931
36.38. role_udt_grants … 932
36.39. role_usage_grants … 932
36.40. routine_privileges … 933
36.41. routines … 933
36.42. schemata … 938
36.43. sequences … 938
36.44. sql_features … 939
36.45. sql_implementation_info … 940
36.46. sql_languages … 940
36.47. sql_packages … 941
36.48. sql_parts … 941
36.49. sql_sizing … 941
36.50. sql_sizing_profiles … 942
36.51. table_constraints … 942
36.52. table_privileges … 943
36.53. tables … 943
36.54. transforms … 944
36.55. triggered_update_columns … 945
36.56. triggers … 945
36.57. udt_privileges … 947
ix
PostgreSQL 12.2 手册
36.58. usage_privileges … 947
36.59. user_defined_types … 948
36.60. user_mapping_options … 949
36.61. user_mappings … 949
36.62. view_column_usage … 950
36.63. view_routine_usage … 950
36.64. view_table_usage … 951
36.65. views … 951
V. 服务器编程 … 953
扩展 SQL … 958
37.1. 扩展性如何工作 … 958
37.2. PostgreSQL类型系统 … 958
37.3. 用户定义的函数 … 959
37.4. 用户定义的过程 … 960
37.5. 查询语言（SQL）函数 … 960
37.6. 函数重载 … 975
37.7. 函数易变性分类 … 975
37.8. 过程语言函数 … 976
37.9. 内部函数 … 977
37.10. C 语言函数 … 977
37.11. 函数优化信息 … 996
37.12. 用户定义的聚集 … 996
37.13. 用户定义的类型 … 1002
37.14. 用户定义的操作符 … 1006
37.15. 操作符优化信息 … 1007
37.16. 索引的接口扩展 … 1011
37.17. 打包相关对象到一个扩展中 … 1022
37.18. 扩展的构建基础设施 … 1028
触发器 … 1033
38.1. 触发器行为概述 … 1033
38.2. 数据改变的可见性 … 1035
38.3. 用 C 编写触发器函数 … 1036
38.4. 一个完整的触发器实例 … 1038
事件触发器 … 1042
39.1. 事件触发器行为总览 … 1042
39.2. 事件触发器触发矩阵 … 1042
39.3. 用 C 编写事件触发器函数 … 1047
39.4. 一个完整的事件触发器例子 … 1048
39.5. 一个表重写事件触发器例子 … 1049
规则系统 … 1051
40.1. 查询树 … 1051
40.2. 视图和规则系统 … 1052
40.3. 物化视图 … 1059
40.4. INSERT、UPDATE和DELETE上的规则 … 1061
40.5. 规则和权限 … 1072
40.6. 规则和命令状态 … 1073
40.7. 规则 vs 触发器 … 1074
过程语言 … 1077
41.1. 安装过程语言 … 1077
PL/pgSQL - SQL过程语言 … 1079
42.1. 综述 … 1079
42.2. PL/pgSQL的结构 … 1080
42.3. 声明 … 1081
42.4. 表达式 … 1087
42.5. 基本语句 … 1087
42.6. 控制结构 … 1094
42.7. 游标 … 1108
42.8. 事务管理 … 1113
x
PostgreSQL 12.2 手册
42.9. 错误和消息 … 1114
42.10. 触发器函数 … 1117
42.11. PL/pgSQL的内部 … 1125
42.12. PL/pgSQL开发提示 … 1128
42.13. 从Oracle PL/SQL 移植 … 1131
PL/Tcl - Tcl 过程语言 … 1140
43.1. 概述 … 1140
43.2. PL/Tcl 函数和参数 … 1140
43.3. PL/Tcl 中的数据值 … 1142
43.4. PL/Tcl 中的全局数据 … 1142
43.5. 从 PL/Tcl 访问数据库 … 1143
43.6. PL/Tcl 中的触发器函数 … 1145
43.7. PL/Tcl 中的事件触发器函数 … 1146
43.8. PL/Tcl 中的错误处理 … 1147
43.9. PL/Tcl中的显式子事务 … 1148
43.10. 事务管理 … 1149
43.11. PL/Tcl配置 … 1149
43.12. Tcl 过程名 … 1149
PL/Perl - Perl 过程语言 … 1151
44.1. PL/Perl 函数和参数 … 1151
44.2. PL/Perl 中的数据值 … 1155
44.3. 内建函数 … 1155
44.4. PL/Perl 中的全局值 … 1160
44.5. 可信的和不可信的 PL/Perl … 1161
44.6. PL/Perl 触发器 … 1162
44.7. PL/Perl 事件触发器 … 1163
44.8. PL/Perl 下面的东西 … 1164
PL/Python - Python 过程语言 … 1166
45.1. Python 2 vs. Python 3 … 1166
45.2. PL/Python 函数 … 1167
45.3. 数据值 … 1168
45.4. 共享数据 … 1173
45.5. 匿名代码块 … 1173
45.6. 触发器函数 … 1173
45.7. 数据库访问 … 1174
45.8. 显式子事务 … 1178
45.9. 事务管理 … 1179
45.10. 实用函数 … 1180
45.11. 环境变量 … 1181
服务器编程接口 … 1182
46.1. 接口函数 … 1182
46.2. 接口支持函数 … 1215
46.3. 内存管理 … 1224
46.4. 事务管理 … 1234
46.5. 数据改变的可见性 … 1237
46.6. 例子 … 1237
后台工作者进程 … 1241
逻辑解码 … 1244
48.1. 逻辑解码的例子 … 1244
48.2. 逻辑解码概念 … 1246
48.3. 流复制协议接口 … 1247
48.4. 逻辑解码的 SQL 接口 … 1247
48.5. 与逻辑解码相关的系统目录 … 1247
48.6. 逻辑解码输出插件 … 1248
48.7. 逻辑解码输出写入器 … 1251
48.8. 逻辑解码的同步复制支持 … 1251
复制进度追踪 … 1252
VI. 参考 … 1253
xi
PostgreSQL 12.2 手册
I. SQL 命令 … 1258
ABORT … 1262
ALTER AGGREGATE … 1263
ALTER COLLATION … 1265
ALTER CONVERSION … 1267
ALTER DATABASE … 1268
ALTER DEFAULT PRIVILEGES … 1270
ALTER DOMAIN … 1273
ALTER EVENT TRIGGER … 1276
ALTER EXTENSION … 1277
ALTER FOREIGN DATA WRAPPER … 1280
ALTER FOREIGN TABLE … 1282
ALTER FUNCTION … 1287
ALTER GROUP … 1291
ALTER INDEX … 1293
ALTER LANGUAGE … 1296
ALTER LARGE OBJECT … 1297
ALTER MATERIALIZED VIEW … 1298
ALTER OPERATOR … 1300
ALTER OPERATOR CLASS … 1302
ALTER OPERATOR FAMILY … 1303
ALTER POLICY … 1307
ALTER PROCEDURE … 1309
ALTER PUBLICATION … 1312
ALTER ROLE … 1314
ALTER ROUTINE … 1318
ALTER RULE … 1319
ALTER SCHEMA … 1320
ALTER SEQUENCE … 1321
ALTER SERVER … 1324
ALTER STATISTICS … 1326
ALTER SUBSCRIPTION … 1327
ALTER SYSTEM … 1329
ALTER TABLE … 1331
ALTER TABLESPACE … 1345
ALTER TEXT SEARCH CONFIGURATION … 1347
ALTER TEXT SEARCH DICTIONARY … 1349
ALTER TEXT SEARCH PARSER … 1351
ALTER TEXT SEARCH TEMPLATE … 1352
ALTER TRIGGER … 1353
ALTER TYPE … 1355
ALTER USER … 1358
ALTER USER MAPPING … 1359
ALTER VIEW … 1360
ANALYZE … 1362
BEGIN … 1365
CALL … 1367
CHECKPOINT … 1368
CLOSE … 1369
CLUSTER … 1370
COMMENT … 1372
COMMIT … 1376
COMMIT PREPARED … 1377
COPY … 1378
CREATE ACCESS METHOD … 1387
CREATE AGGREGATE … 1388
CREATE CAST … 1395
CREATE COLLATION … 1399
xii
PostgreSQL 12.2 手册
CREATE CONVERSION … 1401
CREATE DATABASE … 1403
CREATE DOMAIN … 1406
CREATE EVENT TRIGGER … 1409
CREATE EXTENSION … 1411
CREATE FOREIGN DATA WRAPPER … 1413
CREATE FOREIGN TABLE … 1415
CREATE FUNCTION … 1419
CREATE GROUP … 1426
CREATE INDEX … 1427
CREATE LANGUAGE … 1434
CREATE MATERIALIZED VIEW … 1437
CREATE OPERATOR … 1439
CREATE OPERATOR CLASS … 1442
CREATE OPERATOR FAMILY … 1445
CREATE POLICY … 1446
CREATE PROCEDURE … 1451
CREATE PUBLICATION … 1454
CREATE ROLE … 1456
CREATE RULE … 1460
CREATE SCHEMA … 1463
CREATE SEQUENCE … 1465
CREATE SERVER … 1468
CREATE STATISTICS … 1470
CREATE SUBSCRIPTION … 1472
CREATE TABLE … 1475
CREATE TABLE AS … 1494
CREATE TABLESPACE … 1497
CREATE TEXT SEARCH CONFIGURATION … 1499
CREATE TEXT SEARCH DICTIONARY … 1500
CREATE TEXT SEARCH PARSER … 1502
CREATE TEXT SEARCH TEMPLATE … 1504
CREATE TRANSFORM … 1505
CREATE TRIGGER … 1507
CREATE TYPE … 1513
CREATE USER … 1521
CREATE USER MAPPING … 1522
CREATE VIEW … 1523
DEALLOCATE … 1528
DECLARE … 1529
DELETE … 1532
DISCARD … 1535
DO … 1536
DROP ACCESS METHOD … 1538
DROP AGGREGATE … 1539
DROP CAST … 1541
DROP COLLATION … 1542
DROP CONVERSION … 1543
DROP DATABASE … 1544
DROP DOMAIN … 1545
DROP EVENT TRIGGER … 1546
DROP EXTENSION … 1547
DROP FOREIGN DATA WRAPPER … 1548
DROP FOREIGN TABLE … 1549
DROP FUNCTION … 1550
DROP GROUP … 1552
DROP INDEX … 1553
DROP LANGUAGE … 1555
xiii
PostgreSQL 12.2 手册
DROP MATERIALIZED VIEW … 1556
DROP OPERATOR … 1557
DROP OPERATOR CLASS … 1559
DROP OPERATOR FAMILY … 1561
DROP OWNED … 1563
DROP POLICY … 1564
DROP PROCEDURE … 1565
DROP PUBLICATION … 1567
DROP ROLE … 1568
DROP ROUTINE … 1569
DROP RULE … 1570
DROP SCHEMA … 1571
DROP SEQUENCE … 1572
DROP SERVER … 1573
DROP STATISTICS … 1574
DROP SUBSCRIPTION … 1575
DROP TABLE … 1576
DROP TABLESPACE … 1577
DROP TEXT SEARCH CONFIGURATION … 1578
DROP TEXT SEARCH DICTIONARY … 1579
DROP TEXT SEARCH PARSER … 1580
DROP TEXT SEARCH TEMPLATE … 1581
DROP TRANSFORM … 1582
DROP TRIGGER … 1583
DROP TYPE … 1584
DROP USER … 1585
DROP USER MAPPING … 1586
DROP VIEW … 1587
END … 1588
EXECUTE … 1589
EXPLAIN … 1590
FETCH … 1595
GRANT … 1599
IMPORT FOREIGN SCHEMA … 1604
INSERT … 1606
LISTEN … 1613
LOAD … 1615
LOCK … 1616
MOVE … 1619
NOTIFY … 1621
PREPARE … 1623
PREPARE TRANSACTION … 1625
REASSIGN OWNED … 1627
REFRESH MATERIALIZED VIEW … 1628
REINDEX … 1630
RELEASE SAVEPOINT … 1634
RESET … 1635
REVOKE … 1636
ROLLBACK … 1640
ROLLBACK PREPARED … 1641
ROLLBACK TO SAVEPOINT … 1642
SAVEPOINT … 1644
SECURITY LABEL … 1646
SELECT … 1649
SELECT INTO … 1668
SET … 1670
SET CONSTRAINTS … 1673
SET ROLE … 1674
xiv
PostgreSQL 12.2 手册
SET SESSION AUTHORIZATION … 1676
SET TRANSACTION … 1678
SHOW … 1681
START TRANSACTION … 1683
TRUNCATE … 1684
UNLISTEN … 1686
UPDATE … 1688
VACUUM … 1692
VALUES … 1695
II. PostgreSQL 客户端应用 … 1698
clusterdb … 1699
createdb … 1702
createuser … 1705
dropdb … 1709
dropuser … 1712
ecpg … 1715
pg_basebackup … 1717
pgbench … 1724
pg_config … 1739
pg_dump … 1742
pg_dumpall … 1754
pg_isready … 1760
pg_receivewal … 1762
pg_recvlogical … 1766
pg_restore … 1770
psql … 1778
reindexdb … 1815
vacuumdb … 1818
III. PostgreSQL 服务器应用 … 1822
initdb … 1823
pg_archivecleanup … 1827
pg_checksums … 1829
pg_controldata … 1831
pg_ctl … 1832
pg_resetwal … 1837
pg_rewind … 1840
pg_test_fsync … 1843
pg_test_timing … 1844
pg_upgrade … 1847
pg_waldump … 1854
postgres … 1856
postmaster … 1863
VII. 内部 … 1864
PostgreSQL内部概述 … 1870
50.1. 一个查询的路径 … 1870
50.2. 连接如何建立 … 1870
50.3. 分析器阶段 … 1871
50.4. PostgreSQL规则系统 … 1871
50.5. 规划器/优化器 … 1872
50.6. 执行器 … 1873
系统目录 … 1874
51.1. 概述 … 1874
51.2. pg_aggregate … 1875
51.3. pg_am … 1877
51.4. pg_amop … 1878
51.5. pg_amproc … 1879
51.6. pg_attrdef … 1879
51.7. pg_attribute … 1879
xv
PostgreSQL 12.2 手册
51.8. pg_authid … 1882
51.9. pg_auth_members … 1883
51.10. pg_cast … 1884
51.11. pg_class … 1885
51.12. pg_collation … 1888
51.13. pg_constraint … 1889
51.14. pg_conversion … 1891
51.15. pg_database … 1891
51.16. pg_db_role_setting … 1893
51.17. pg_default_acl … 1893
51.18. pg_depend … 1894
51.19. pg_description … 1895
51.20. pg_enum … 1896
51.21. pg_event_trigger … 1896
51.22. pg_extension … 1897
51.23. pg_foreign_data_wrapper … 1897
51.24. pg_foreign_server … 1898
51.25. pg_foreign_table … 1898
51.26. pg_index … 1899
51.27. pg_inherits … 1901
51.28. pg_init_privs … 1901
51.29. pg_language … 1902
51.30. pg_largeobject … 1903
51.31. pg_largeobject_metadata … 1903
51.32. pg_namespace … 1903
51.33. pg_opclass … 1904
51.34. pg_operator … 1904
51.35. pg_opfamily … 1905
51.36. pg_partitioned_table … 1906
51.37. pg_pltemplate … 1906
51.38. pg_policy … 1907
51.39. pg_proc … 1908
51.40. pg_publication … 1911
51.41. pg_publication_rel … 1912
51.42. pg_range … 1912
51.43. pg_replication_origin … 1912
51.44. pg_rewrite … 1913
51.45. pg_seclabel … 1913
51.46. pg_sequence … 1914
51.47. pg_shdepend … 1914
51.48. pg_shdescription … 1915
51.49. pg_shseclabel … 1916
51.50. pg_statistic … 1916
51.51. pg_statistic_ext … 1918
51.52. pg_statistic_ext_data … 1918
51.53. pg_subscription … 1919
51.54. pg_subscription_rel … 1919
51.55. pg_tablespace … 1920
51.56. pg_transform … 1920
51.57. pg_trigger … 1921
51.58. pg_ts_config … 1922
51.59. pg_ts_config_map … 1923
51.60. pg_ts_dict … 1923
51.61. pg_ts_parser … 1924
51.62. pg_ts_template … 1924
51.63. pg_type … 1924
51.64. pg_user_mapping … 1930
51.65. 系统视图 … 1930
xvi
PostgreSQL 12.2 手册
51.66. pg_available_extensions … 1931
51.67. pg_available_extension_versions … 1932
51.68. pg_config … 1932
51.69. pg_cursors … 1933
51.70. pg_file_settings … 1933
51.71. pg_group … 1934
51.72. pg_hba_file_rules … 1934
51.73. pg_indexes … 1935
51.74. pg_locks … 1935
51.75. pg_matviews … 1938
51.76. pg_policies … 1938
51.77. pg_prepared_statements … 1939
51.78. pg_prepared_xacts … 1939
51.79. pg_publication_tables … 1940
51.80. pg_replication_origin_status … 1940
51.81. pg_replication_slots … 1941
51.82. pg_roles … 1942
51.83. pg_rules … 1943
51.84. pg_seclabels … 1943
51.85. pg_sequences … 1944
51.86. pg_settings … 1944
51.87. pg_shadow … 1946
51.88. pg_stats … 1947
51.89. pg_stats_ext … 1948
51.90. pg_tables … 1950
51.91. pg_timezone_abbrevs … 1950
51.92. pg_timezone_names … 1951
51.93. pg_user … 1951
51.94. pg_user_mappings … 1951
51.95. pg_views … 1952
前端/后端协议 … 1953
52.1. 概述 … 1953
52.2. 消息流 … 1954
52.3. SASL认证 … 1965
52.4. 流复制协议 … 1966
52.5. 逻辑流复制协议 … 1973
52.6. 消息数据类型 … 1973
52.7. 消息格式 … 1974
52.8. 错误和通知消息域 … 1990
52.9. 逻辑复制消息格式 … 1992
52.10. 自协议2.0以来的变化总结 … 1996
PostgreSQL编码习惯 … 1998
53.1. 格式化 … 1998
53.2. 在服务器中报告错误 … 1998
53.3. 错误消息风格指导 … 2001
53.4. 其他编码习惯 … 2005
本国语言支持 … 2007
54.1. 给翻译者 … 2007
54.2. 给编程者 … 2009
编写一个过程语言处理器 … 2012
编写一个外部数据包装器 … 2015
56.1. 外部数据包装器函数 … 2015
56.2. 外部数据包装器回调例程 … 2015
56.3. 外部数据包装器助手函数 … 2027
56.4. 外部数据包装器查询规划 … 2028
56.5. 外部数据包装器中的行锁定 … 2030
编写一种表采样方法 … 2032
57.1. 采样方法支持函数 … 2032
xvii
PostgreSQL 12.2 手册
编写一个自定义扫描提供者 … 2035
58.1. 创建自定义扫描路径 … 2035
58.2. 创建自定义扫描计划 … 2036
58.3. 执行自定义扫描 … 2037
遗传查询优化器 … 2040
59.1. 将查询处理看成是一个复杂的优化问题 … 2040
59.2. 遗传算法 … 2040
59.3. PostgreSQL 中的遗传查询优化（GEQO） … 2041
59.4. 进一步阅读 … 2042
表访问方法接口定义 … 2043
索引访问方法接口定义 … 2044
61.1. 索引的基本 API 结构 … 2044
61.2. 索引访问方法函数 … 2046
61.3. 索引扫描 … 2051
61.4. 索引锁定考虑 … 2052
61.5. 索引唯一性检查 … 2053
61.6. 索引开销估计函数 … 2054
通用WAL 记录 … 2057
B-树索引 … 2059
63.1. 简介 … 2059
63.2. B-树操作符类的行为 … 2059
63.3. B-树支持函数 … 2060
63.4. 实现 … 2061
GiST 索引 … 2062
64.1. 简介 … 2062
64.2. 内建操作符类 … 2062
64.3. 可扩展性 … 2063
64.4. 实现 … 2071
64.5. 示例 … 2071
SP-GiST索引 … 2073
65.1. 简介 … 2073
65.2. 内建操作符类 … 2073
65.3. 可扩展性 … 2073
65.4. 实现 … 2080
65.5. 例子 … 2081
GIN 索引 … 2082
66.1. 简介 … 2082
66.2. 内建操作符类 … 2082
66.3. 可扩展性 … 2082
66.4. 实现 … 2085
66.5. GIN 提示和技巧 … 2086
66.6. 限制 … 2086
66.7. 例子 … 2086
BRIN 索引 … 2088
67.1. 简介 … 2088
67.2. 内建操作符类 … 2088
67.3. 可扩展性 … 2089
数据库物理存储 … 2093
68.1. 数据库文件布局 … 2093
68.2. TOAST … 2095
68.3. 空闲空间映射 … 2097
68.4. 可见性映射 … 2097
68.5. 初始化分支 … 2097
68.6. 数据库页面布局 … 2098
系统目录声明和初始内容 … 2101
69.1. 系统目录声明规则 … 2101
69.2. 系统目录初始数据 … 2102
69.3. BKI文件格式 … 2106
xviii
PostgreSQL 12.2 手册
69.4. BKI命令 … 2106
69.5. 自举BKI文件的结构 … 2107
69.6. BKI例子 … 2108
规划器如何使用统计信息 … 2109
70.1. 行估计例子 … 2109
70.2. 多变量统计例子 … 2114
70.3. 规划器统计和安全 … 2117
VIII. 附录 … 2118
A. PostgreSQL错误代码 … 2124
B. 日期/时间支持 … 2132
B.1. 日期/时间输入解释 … 2132
B.2. 处理无效或不明确的时间戳 … 2133
B.3. 日期/时间关键词 … 2134
B.4. 日期/时间配置文件 … 2134
B.5. 单位的历史 … 2136
C. SQL关键词 … 2138
D. SQL 符合性 … 2163
D.1. 已支持特性 … 2164
D.2. 未支持特性 … 2182
D.3. XML对于SQL的限制和符合性/XML … 2196
E. 版本说明 … 2200
E.1. 版本 12.2 … 2200
E.2. 版本 12.1 … 2205
E.3. 版本 12 … 2208
E.4. 先前的版本 … 2229
F. 额外提供的模块 … 2230
F.1. adminpack … 2230
F.2. amcheck … 2231
F.3. auth_delay … 2234
F.4. auto_explain … 2234
F.5. bloom … 2237
F.6. btree_gin … 2240
F.7. btree_gist … 2240
F.8. citext … 2242
F.9. cube … 2244
F.10. dblink … 2248
F.11. dict_int … 2277
F.12. dict_xsyn … 2277
F.13. earthdistance … 2279
F.14. file_fdw … 2280
F.15. fuzzystrmatch … 2282
F.16. hstore … 2285
F.17. intagg … 2291
F.18. intarray … 2292
F.19. isn … 2294
F.20. lo … 2297
F.21. ltree … 2298
F.22. pageinspect … 2304
F.23. passwordcheck … 2311
F.24. pg_buffercache … 2312
F.25. pgcrypto … 2313
F.26. pg_freespacemap … 2324
F.27. pg_prewarm … 2325
F.28. pgrowlocks … 2326
F.29. pg_stat_statements … 2327
F.30. pgstattuple … 2333
F.31. pg_trgm … 2336
F.32. pg_visibility … 2342
xix
PostgreSQL 12.2 手册
F.33. postgres_fdw … 2343
F.34. seg … 2348
F.35. sepgsql … 2350
F.36. spi … 2357
F.37. sslinfo … 2359
F.38. tablefunc … 2360
F.39. tcn … 2369
F.40. test_decoding … 2370
F.41. tsm_system_rows … 2371
F.42. tsm_system_time … 2371
F.43. unaccent … 2372
F.44. uuid-ossp … 2373
F.45. xml2 … 2375
G. 额外提供的程序 … 2380
G.1. 客户端应用 … 2380
G.2. 服务器应用 … 2387
H. 外部项目 … 2391
H.1. 客户端接口 … 2391
H.2. 管理工具 … 2391
H.3. 过程语言 … 2391
H.4. 扩展 … 2392
I. 源代码仓库 … 2393
I.1. 通过Git得到源码 … 2393
J. 文档 … 2394
J.1. DocBook … 2394
J.2. 工具集 … 2394
J.3. 编译文档 … 2396
J.4. 文档创作 … 2397
J.5. 样式指导 … 2397
K. PostgreSQL限制 … 2400
L. 首字母缩写词 … 2401
参考书目 … 2407
索引 … 2409
xx
插图清单
9.1. 转换 SQL/XML 输出到 HTML 的 XSLT 样式表 … 271
59.1. 一种遗传算法的结构图 … 2041
66.1. GIN内部 … 2085
68.1. 页面布局 … 2099
xxi
表格清单
4.1. 反斜线转义序列 … 33
4.2. 操作符优先级（从高到低） … 38
5.1. ACL 权限缩写 … 70
5.2. 访问权限摘要 … 71
8.1. 数据类型 … 128
8.2. 数字类型 … 129
8.3. 货币类型 … 134
8.4. 字符类型 … 134
8.5. 特殊字符类型 … 136
8.6. 二进制数据类型 … 136
8.7. bytea文字转义字节 … 137
8.8. bytea输出转义字节 … 138
8.9. 日期/时间类型 … 138
8.10. 日期输入 … 139
8.11. 时间输入 … 140
8.12. 时区输入 … 140
8.13. 特殊日期/时间输入 … 142
8.14. 日期/时间输出风格 … 142
8.15. 日期顺序习惯 … 143
8.16. ISO 8601 间隔单位缩写 … 145
8.17. 间隔输入 … 145
8.18. 间隔输出风格例子 … 146
8.19. 布尔数据类型 … 146
8.20. 几何类型 … 149
8.21. 网络地址类型 … 151
8.22. cidr类型输入例子 … 152
8.23. JSON 基本类型和相应的PostgreSQL类型 … 160
8.24. jsonpath 变量 … 167
8.25. jsonpath Accessors … 167
8.26. 对象标识符类型 … 188
8.27. 伪类型 … 189
9.1. 比较操作符 … 191
9.2. 比较谓词 … 192
9.3. 比较函数 … 194
9.4. 数学操作符 … 194
9.5. 数学函数 … 195
9.6. 随机函数 … 196
9.7. 三角函数 … 197
9.8. 双曲函数 … 197
9.9. SQL字符串函数和操作符 … 198
9.10. 其他字符串函数 … 199
9.11. 内建转换 … 204
9.12. SQL二进制串函数和操作符 … 210
9.13. 其他二进制串函数 … 210
9.14. 位串操作符 … 212
9.15. 正则表达式匹配操作符 … 215
9.16. 正则表达式原子 … 219
9.17. 正则表达式量词 … 219
9.18. 正则表达式约束 … 220
9.19. 正则表达式字符项逃逸 … 222
9.20. 正则表达式类缩写逃逸 … 222
9.21. 正则表达式约束逃逸 … 223
9.22. 正则表达式后引用 … 223
9.23. ARE 嵌入选项字母 … 223
9.24. 格式化函数 … 227
xxii
PostgreSQL 12.2 手册
9.25. 用于日期/时间格式化的模板模式 … 228
9.26. 用于日期/时间格式化的模板模式修饰语 … 230
9.27. 用于数字格式化的模板模式 … 232
9.28. 用于数字格式化的模板模式修饰语 … 233
9.29. to_char例子 … 233
9.30. 日期/时间操作符 … 234
9.31. 日期/时间函数 … 235
9.32. AT TIME ZONE变体 … 244
9.33. 枚举支持函数 … 247
9.34. 几何操作符 … 248
9.35. 几何函数 … 249
9.36. 几何类型转换函数 … 250
9.37. cidr和inet操作符 … 251
9.38. cidr和inet函数 … 252
9.39. macaddr函数 … 253
9.40. macaddr8函数 … 253
9.41. 文本搜索操作符 … 253
9.42. 文本搜索函数 … 254
9.43. 文本搜索调试函数 … 258
9.44. json和jsonb 操作符 … 272
9.45. 额外的jsonb操作符 … 273
9.46. JSON 创建函数 … 274
9.47. JSON 处理 … 275
9.48. jsonpath 运算符和方法 … 283
9.49. jsonpath 筛选表达式元素 … 284
9.50. 序列函数 … 285
9.51. 数组操作符 … 290
9.52. 数组函数 … 291
9.53. 范围操作符 … 293
9.54. 范围函数 … 294
9.55. 通用聚集函数 … 295
9.56. 用于统计的聚集函数 … 297
9.57. 有序集聚集函数 … 298
9.58. 假想集聚集函数 … 299
9.59. 分组操作 … 300
9.60. 通用窗口函数 … 301
9.61. 级数生成函数 … 307
9.62. 下标生成函数 … 308
9.63. 会话信息函数 … 310
9.64. 访问权限查询函数 … 313
9.65. aclitem Operators … 315
9.66. aclitem Functions … 315
9.67. 模式可见性查询函数 … 316
9.68. 系统目录信息函数 … 317
9.69. 索引列属性 … 319
9.70. 索引性质 … 320
9.71. 索引访问方法性质 … 320
9.72. 对象信息和定位函数 … 321
9.73. 注释信息函数 … 322
9.74. 事务 ID 和快照 … 322
9.75. 快照成分 … 323
9.76. 已提交事务信息 … 323
9.77. 控制数据函数 … 323
9.78. pg_control_checkpoint列 … 324
9.79. pg_control_system列 … 324
9.80. pg_control_init列 … 324
9.81. pg_control_recovery列 … 325
9.82. 配置设定函数 … 325
xxiii
PostgreSQL 12.2 手册
9.83. 服务器信号函数 … 326
9.84. 备份控制函数 … 327
9.85. 恢复信息函数 … 329
9.86. 恢复控制函数 … 329
9.87. 快照同步函数 … 330
9.88. 复制 SQL 函数 … 331
9.89. 数据库对象尺寸函数 … 334
9.90. 数据库对象定位函数 … 336
9.91. 排序规则管理函数 … 336
9.92. 分区信息函数 … 336
9.93. 索引维护函数 … 337
9.94. 通用文件访问函数 … 338
9.95. 咨询锁函数 … 339
9.96. 表重写信息 … 344
12.1. 默认解析器的记号类型 … 388
13.1. 事务隔离级别 … 408
13.2. 冲突的锁模式 … 414
13.3. 冲突的行级锁 … 416
18.1. System V IPC参数 … 479
18.2. SSL 服务器文件用法 … 493
19.1. 消息严重级别 … 537
19.2. 短选项键 … 560
21.1. 默认角色 … 584
23.1. PostgreSQL字符集 … 599
23.2. 客户/服务器字符集转换 … 602
26.1. 高可用、负载均衡和复制特性矩阵 … 630
27.1. 动态统计视图 … 649
27.2. 已收集统计信息的视图 … 650
27.3. pg_stat_activity 视图 … 652
27.4. wait_event 描述 … 655
27.5. pg_stat_replication 视图 … 664
27.6. pg_stat_wal_receiver 视图 … 667
27.7. pg_stat_subscription视图 … 668
27.8. pg_stat_ssl视图 … 668
27.9. pg_stat_gssapi 视图 … 669
27.10. pg_stat_archiver视图 … 669
27.11. pg_stat_bgwriter视图 … 670
27.12. pg_stat_database视图 … 670
27.13. pg_stat_database_conflicts视图 … 672
27.14. pg_stat_all_tables视图 … 672
27.15. pg_stat_all_indexes视图 … 673
27.16. pg_statio_all_tables视图 … 674
27.17. pg_statio_all_indexes视图 … 674
27.18. pg_statio_all_sequences视图 … 675
27.19. pg_stat_user_functions视图 … 675
27.20. 额外统计函数 … 676
27.21. 针对每个后端的统计函数 … 677
27.22. pg_stat_progress_create_index 视图 … 678
27.23. CREATE INDEX 的阶段 … 678
27.24. pg_stat_progress_vacuum视图 … 679
27.25. VACUUM的阶段 … 680
27.26. pg_stat_progress_cluster 视图 … 681
27.27. CLUSTER 和 VACUUM FULL 阶段 … 682
27.28. 内建 DTrace 探针 … 682
27.29. 定义用在探针参数中的类型 … 688
33.1. SSL 模式描述 … 780
33.2. Libpq/客户端 SSL 文件用法 … 781
34.1. 面向 SQL 的大对象函数 … 798
xxiv
PostgreSQL 12.2 手册
35.1. 在 PostgreSQL 数据类型和 C 变量类型之间映射 … 813
35.2. PGTYPESdate_from_asc的合法输入格式 … 830
35.3. PGTYPESdate_fmt_asc的合法输入格式 … 832
35.4. rdefmtdate的合法输入格式 … 833
35.5. PGTYPEStimestamp_from_asc的合法输入格式 … 834
36.1. information_schema_catalog_name列 … 907
36.2. administrable_role_authorizations列 … 907
36.3. applicable_roles列 … 907
36.4. attributes列 … 908
36.5. character_sets列 … 911
36.6. check_constraint_routine_usage列 … 911
36.7. check_constraints列 … 912
36.8. collations列 … 912
36.9. collation_character_set_applicability列 … 912
36.10. column_column_usage Columns … 913
36.11. column_domain_usage列 … 913
36.12. column_options列 … 913
36.13. column_privileges列 … 914
36.14. column_udt_usage列 … 914
36.15. columns列 … 914
36.16. constraint_column_usage列 … 918
36.17. constraint_table_usage列 … 919
36.18. data_type_privileges列 … 919
36.19. domain_constraints列 … 920
36.20. domain_udt_usage列 … 920
36.21. domains列 … 920
36.22. element_types列 … 923
36.23. enabled_roles列 … 925
36.24. foreign_data_wrapper_options列 … 925
36.25. foreign_data_wrappers列 … 925
36.26. foreign_server_options列 … 926
36.27. foreign_servers列 … 926
36.28. foreign_table_options列 … 926
36.29. foreign_tables列 … 927
36.30. key_column_usage列 … 927
36.31. parameters列 … 928
36.32. referential_constraints列 … 930
36.33. role_column_grants列 … 930
36.34. role_routine_grants列 … 931
36.35. role_table_grants列 … 931
36.36. role_udt_grants列 … 932
36.37. role_usage_grants列 … 932
36.38. routine_privileges列 … 933
36.39. routines列 … 933
36.40. schemata列 … 938
36.41. sequences列 … 938
36.42. sql_features列 … 939
36.43. sql_implementation_info列 … 940
36.44. sql_languages列 … 940
36.45. sql_packages列 … 941
36.46. sql_parts列 … 941
36.47. sql_sizing列 … 942
36.48. sql_sizing_profiles列 … 942
36.49. table_constraints列 … 942
36.50. table_privileges列 … 943
36.51. tables列 … 943
36.52. transforms 列 … 944
36.53. triggered_update_columns列 … 945
xxv
PostgreSQL 12.2 手册
36.54. triggers列 … 945
36.55. udt_privileges列 … 947
36.56. usage_privileges列 … 947
36.57. user_defined_types列 … 948
36.58. user_mapping_options列 … 949
36.59. user_mappings列 … 950
36.60. view_column_usage列 … 950
36.61. view_routine_usage列 … 950
36.62. view_table_usage列 … 951
36.63. views列 … 951
37.1. 内建 SQL 类型等效的 C 类型 … 980
37.2. B-树策略 … 1011
37.3. 哈希策略 … 1012
37.4. GiST 二维“R-树” 策略 … 1012
37.5. SP-GiST 点策略 … 1012
37.6. GIN 数组策略 … 1012
37.7. BRIN 最小最大策略 … 1013
37.8. B-树支持函数 … 1013
37.9. 哈希支持函数 … 1013
37.10. GiST 支持函数 … 1014
37.11. SP-GiST 支持函数 … 1014
37.12. GIN 支持函数 … 1014
37.13. BRIN 支持函数 … 1015
39.1. 支持事件触发器的命令标签 … 1043
42.1. 可用的诊断项 … 1093
42.2. 错误诊断项 … 1107
按命令类型应用的策略 … 1448
自动变量 … 1731
按优先级升序排列的pgbench操作符 … 1733
pgbench 函数 … 1733
51.1. 系统目录 … 1874
51.2. pg_aggregate的列 … 1876
51.3. pg_am的列 … 1877
51.4. pg_amop的列 … 1878
51.5. pg_amproc的列 … 1879
51.6. pg_attrdef的列 … 1879
51.7. pg_attribute的列 … 1880
51.8. pg_authid的列 … 1883
51.9. pg_auth_members的列 … 1884
51.10. pg_cast的列 … 1884
51.11. pg_class的列 … 1885
51.12. pg_collation的列 … 1888
51.13. pg_constraint的列 … 1889
51.14. pg_conversion的列 … 1891
51.15. pg_database的列 … 1891
51.16. pg_db_role_setting的列 … 1893
51.17. pg_default_acl的列 … 1893
51.18. pg_depend的列 … 1894
51.19. pg_description的列 … 1896
51.20. pg_enum的列 … 1896
51.21. pg_event_trigger的列 … 1896
51.22. pg_extension的列 … 1897
51.23. pg_foreign_data_wrapper的列 … 1897
51.24. pg_foreign_server的列 … 1898
51.25. pg_foreign_table的列 … 1899
51.26. pg_index的列 … 1899
51.27. pg_inherits的列 … 1901
51.28. pg_init_privs列 … 1902
xxvi
PostgreSQL 12.2 手册
51.29. pg_language的列 … 1902
51.30. pg_largeobject的列 … 1903
51.31. pg_largeobject_metadata的列 … 1903
51.32. pg_namespace的列 … 1904
51.33. pg_opclass的列 … 1904
51.34. pg_operator的列 … 1904
51.35. pg_opfamily的列 … 1905
51.36. pg_partitioned_table列 … 1906
51.37. pg_pltemplate的列 … 1907
51.38. pg_policy列 … 1907
51.39. pg_proc的列 … 1908
51.40. pg_publication的列 … 1911
51.41. pg_publication_rel列 … 1912
51.42. pg_range的列 … 1912
51.43. pg_replication_origin的列 … 1912
51.44. pg_rewrite的列 … 1913
51.45. pg_seclabel的列 … 1913
51.46. pg_sequence的列 … 1914
51.47. pg_shdepend的列 … 1914
51.48. pg_shdescription的列 … 1915
51.49. pg_shseclabel的列 … 1916
51.50. pg_statistic的列 … 1917
51.51. pg_statistic_ext的列 … 1918
51.52. pg_statistic_ext_data Columns … 1919
51.53. pg_subscription的列 … 1919
51.54. pg_subscription_rel的列 … 1920
51.55. pg_tablespace的列 … 1920
51.56. pg_transform的列 … 1920
51.57. pg_trigger的列 … 1921
51.58. pg_ts_config的列 … 1923
51.59. pg_ts_config_map的列 … 1923
51.60. pg_ts_dict的列 … 1923
51.61. pg_ts_parser的列 … 1924
51.62. pg_ts_template的列 … 1924
51.63. pg_type的列 … 1925
51.64. typcategory编码 … 1930
51.65. pg_user_mapping的列 … 1930
51.66. 系统视图 … 1931
51.67. pg_available_extensions的列 … 1932
51.68. pg_available_extension_versions的列 … 1932
51.69. pg_config列 … 1932
51.70. pg_cursors的列 … 1933
51.71. pg_file_settings的列 … 1934
51.72. pg_group的列 … 1934
51.73. pg_hba_file_rules的列 … 1934
51.74. pg_indexes的列 … 1935
51.75. pg_locks的列 … 1936
51.76. pg_matviews的列 … 1938
51.77. pg_policies的列 … 1938
51.78. pg_prepared_statements的列 … 1939
51.79. pg_prepared_xacts的列 … 1940
51.80. pg_publication_tables的列 … 1940
51.81. pg_replication_origin_status的列 … 1940
51.82. pg_replication_slots的列 … 1941
51.83. pg_roles的列 … 1942
51.84. pg_rules的列 … 1943
51.85. pg_seclabels的列 … 1943
51.86. pg_sequences的列 … 1944
xxvii
PostgreSQL 12.2 手册
51.87. pg_settings的列 … 1944
51.88. pg_shadow的列 … 1946
51.89. pg_stats的列 … 1947
51.90. pg_stats_ext的列 … 1949
51.91. pg_tables的列 … 1950
51.92. pg_timezone_abbrevs的列 … 1950
51.93. pg_timezone_names的列 … 1951
51.94. pg_user的列 … 1951
51.95. pg_user_mappings的列 … 1951
51.96. pg_views的列 … 1952
64.1. 内建GiST操作符类 … 2062
65.1. 内建 SP-GiST 操作符类 … 2073
66.1. 内建GIN操作符类 … 2082
67.1. 内建 BRIN 操作符类 … 2089
67.2. Minmax 操作符类的函数和支持编号 … 2090
67.3. Inclusion 操作符类的函数和支持编号 … 2091
68.1. PGDATA的内容 … 2093
68.2. 页面布局 … 2098
68.3. PageHeaderData布局 … 2098
68.4. HeapTupleHeaderData布局 … 2100
A.1. PostgreSQL错误代码 … 2124
B.1. 月份名称 … 2134
B.2. 一周内每一天的名称 … 2134
B.3. 日期/时间域修饰语 … 2134
C.1. SQL Key Words … 2138
F.1. adminpack 函数 … 2231
F.2. 立方体外部表示 … 2244
F.3. 立方体操作符 … 2244
F.4. 立方体函数 … 2246
F.5. 基于立方体的地球距离函数 … 2279
F.6. 基于点的地球距离操作符 … 2280
F.7. hstore 操作符 … 2286
F.8. hstore 函数 … 2287
F.9. intarray 函数 … 2292
F.10. intarray 操作符 … 2292
F.11. isn 数据类型 … 2294
F.12. isn 函数 … 2296
F.13. ltree 操作符 … 2300
F.14. ltree 函数 … 2301
F.15. pg_buffercache 列 … 2312
F.16. crypt()支持的算法 … 2314
F.17. crypt()的迭代计数 … 2315
F.18. 哈希算法速度 … 2315
F.19. 使用和不用 OpenSSL 的功能总结 … 2322
F.20. pgrowlocks 输出列 … 2326
F.21. pg_stat_statements列 … 2328
F.22. pgstattuple 输出列 … 2333
F.23. pgstattuple_approx输出列 … 2336
F.24. pg_trgm函数 … 2337
F.25. pg_trgm操作符 … 2338
F.26. seg外部表达 … 2348
F.27. 合法seg输入的例子 … 2349
F.28. Seg GiST 操作符 … 2349
F.29. Sepgsql 函数 … 2356
F.30. tablefunc函数 … 2360
F.31. connectby 参数 … 2367
F.32. 用于 UUID 产生的函数 … 2374
F.33. 返回 UUID 常量的函数 … 2374
xxviii
PostgreSQL 12.2 手册
F.34. 函数 … 2375
F.35. xpath_table 参数 … 2376
H.1. 外部维护的客户端接口 … 2391
H.2. 外部维护的过程语言 … 2392
K.1. PostgreSQL限制 … 2400
xxix
范例清单
8.1. 使用字符类型 … 135
8.2. 使用boolean类型 … 147
8.3. 使用位串类型 … 154
10.1. 阶乘操作符类型决定 … 347
10.2. 字符串连接操作符类型决定 … 347
10.3. 绝对值与否定操作符类型决定 … 348
10.4. 数组包含操作符类型决定 … 349
10.5. 域类型上的自定义操作符 … 349
10.6. 圆整函数参数类型决定 … 351
10.7. 可变函数决定 … 351
10.8. 子串函数类型决定 … 352
10.9. character存储类型转换 … 353
10.10. 联合中未指定类型的类型决定 … 354
10.11. 简单联合中的类型决定 … 354
10.12. 可换位联合中的类型决定 … 355
10.13. 嵌套合并中的类型决定 … 355
11.1. 建立一个部分索引来排除公值 … 363
11.2. 建立一个部分索引来排除不感兴趣的值 … 364
11.3. 建立一个部分唯一索引 … 364
20.1. 示例 pg_hba.conf 项 … 567
20.2. 一个示例 pg_ident.conf 文件 … 569
33.1. libpq 例子程序 1 … 784
33.2. libpq例子程序 2 … 786
33.3. libpq例子程序 3 … 789
34.1. 用libpq操作大对象的例子程序 … 799
35.1. 示例 SQLDA 程序 … 849
35.2. 访问大对象的 ECPG 程序 … 862
41.1. PL/Perl的手工安装 … 1078
42.1. 在动态查询中引用值 … 1091
42.2. UPDATE/INSERT的异常 … 1106
42.3. 一个 PL/pgSQL 触发器函数 … 1118
42.4. 一个用于审计的 PL/pgSQL 触发器函数 … 1119
42.5. 一个用于审计的 PL/pgSQL 视图触发器函数 … 1120
42.6. 一个 PL/pgSQL 用于维护汇总表的触发器函数 … 1121
42.7. 用传递表进行审计 … 1123
42.8. 一个 PL/pgSQL 事件触发器函数 … 1124
42.9. 从PL/SQL移植一个简单的函数到PL/pgSQL … 1132
42.10. 从PL/SQL移植一个创建另一个函数的函数到PL/pgSQL … 1133
42.11. 从PL/SQL移植一个带有字符串操作以及OUT参数的过程到PL/pgSQL … 1134
42.12. 从PL/SQL移植一个过程到PL/pgSQL … 1136
F.1. 为 PostgreSQL CSV 日志创建一个外部表 … 2282
xxx
前言
本书是PostgreSQL的官方文档。它是PostgreSQL开发人员和其它志愿者与PostgreSQL的开
发并行编写的。它描述了当前版本的PostgreSQL官方支持的所有功能。
为了能够管理有关PostgreSQL的大量信息，本书被组织成了几个部分。每个部分都是针对不
同层次的用户，或者说针对具有不同阶段PostgreSQL体验的用户：
• 第 I 部分是一个给新用户的非正式介绍。
• 第 II 部分记载了SQL查询语言环境，包括数据类型和函数，以及用户级别的性能调优。
每个 PostgreSQL用户都应该阅读这些内容。
• 第 III 部分描述服务器的安装和管理。每个运行PostgreSQL服务器的人，不管是个人使用
还是为别人维护，都应该阅读这部分内容。
• 第 IV 部分描述PostgreSQL客户端程序的编程接口。
• 第 V 部分包含为高级用户准备的信息，比如服务器的扩展能力等。其中的内容包括用户
定义数据类型和函数等。
• 第 VI 部分包含有关 SQL 命令、客户端和服务器程序的参考信息。这部分以按照命令或程
序排序的结构化信息支持其它部分。
• 第 VII 部分包含可用于PostgreSQL开发人员的各类信息。
何为PostgreSQL？
PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES，版本 4.21为基础的对象关
系型数据库管理系统（ORDBMS）。POSTGRES 领先的许多概念在很久以后才出现在一些商业
数据库系统中。
PostgreSQL是最初的伯克利代码的开源继承者。它支持大部分 SQL 标准并且提供了许多现代
特性：
• 复杂查询
• 外键
• 触发器
• 可更新视图
• 事务完整性
• 多版本并发控制
同样，PostgreSQL可以用许多方法扩展，比如，通过增加新的：
• 数据类型
• 函数
• 操作符
• 聚集函数
• 索引方法
• 过程语言
并且，因为自由宽大的许可证，任何人都可以以任何目的免费使用、修改和分发
PostgreSQL，不管是私用、商用还是学术研究目的。
PostgreSQL简史
1
http://db.cs.berkeley.edu/postgres.html
xxxi
前言
现在被称为PostgreSQL的对象-关系型数据库管理系统是从加州大学伯克利分校写的
POSTGRES软件包发展而来的。经过二十多年的发展，PostgreSQL是世界上可以获得的最先进
的开源数据库。
2.1. 伯克利的POSTGRES项目
由Michael Stonebraker教授领导的POSTGRES项目是由防务高级研究项目局（DARPA）、
陆军研究办公室（ARO）、国家科学基金（NSF）以及 ESL, Inc 共
同赞助的。 POSTGRES的实现始于 1986 年。该系统最初的概念详见[ston86]
。最初的数据模型定义见[rowe87]。当时的规则系统设计在[ston87a]里描述。存储管理器
的理论基础和体系结构在[ston87b]里有详细描述。
从那以后，POSTGRES经历了几次主要的版本更新。第一个“演示性”系统在 1987
年便可使用了，并且在 1988 年的ACM-SIGMOD大会上展出。
在 1989 年6月发布了版本 1（见[ston90a]）给一些外部的用户
使用。为了回应用户对第一个规则系统（[ston89]）的批评，规则系统被重新设计了
（[ston90b]），在1990年6月发布了使用新规则系统的版本 2。版本 3 在1991年出现，增
加了多存储管理器的支持，并且改进了查询执行器、重写了规则系统。直到Postgres95发
布前（见下文）的后续版本大多把工作都集中在移植性和可靠性上。
POSTGRES已经被用于实现很多不同的研究和生产应用。这些应用包括：一个财务数据分析
系统、一个喷气引擎性能监控软件包、一个小行星跟踪数据库、一个医疗信息数据库和一些
地理信息系统。POSTGRES还被许多大学用于教学用途。最后，Illustra Information
Technologies（后来并入Informix2，而Informix3现在被IBM4所拥有）拿到代码并使之商业
化。在 1992 年末POSTGRES成为Sequoia 2000科学计算项目5的主要数据管理器。
在 1993 年间，外部用户社区的数量几乎翻番。随着用户的增加，用于源代码维护的时间日
益增加并占用了太多本应该用于数据库研究的时间，为了减少支持的负担，伯克利的
POSTGRES项目在版本 4.2 时正式终止。
2.2. Postgres95
在 1994 年，Andrew Yu 和 Jolly Chen 向POSTGRES中增加了 SQL 语言的解释器。并随后
用新名字Postgres95将源代码发布到互联网上供大家使用，成为最初POSTGRES伯克利代码
的开源继承者。
Postgres95的源代码都是完全的 ANSI C，而且代码量减少了25%。许多内部修改提高了性能
和可维护性。Postgres95的1.0.x版本在进行 Wisconsin Benchmark 测试时大概比POSTGRES
的版本4.2 快 30-50%。除了修正了一些错误，下面的是一些主要提升：
• 原来的查询语言 PostQUEL 被SQL取代（在服务器端实现）。接口库libpq被按照PostQUEL
命名。在PostgreSQL之前还不支持子查询（见下文），但它们可以在Postgres95中由用户
定义的SQL函数模拟。聚集函数被重新实现。同时还增加了对GROUP BY 查询子句的支持。
• 新增加了一个利用GNU的Readline进行交互 SQL 查询的程序（psql）。这个程序很大程度
上取代了老的monitor程序。
• 增加了新的前端库（libpgtcl），用以支持基于Tcl的客户端。一个样本
shell（pgtclsh），提供了新的 Tcl 命令用于Tcl程序和Postgres95服务器之间的交互。
• 彻底重写了大对象的接口。保留了将大对象倒转（Inversion ）作为存储大对象的唯一机
制（去掉了倒转（Inversion ）文件系统）。
• 去掉了实例级的规则系统。但规则仍然以重写规则的形式存在。
2
https://www.ibm.com/analytics/informix
3
https://www.ibm.com/analytics/informix
4
https://www.ibm.com/
5
http://meteora.ucsd.edu/s2k/s2k_home.html
xxxii
前言
• 在发布的源码中增加了一个介绍SQL和Postgres95特性的简短教程。
• 用GNU的make（取代了BSD的make）来编译。Postgres95可以使用不打补丁的GCC编译（修
正了双精度数据对齐问题）。
2.3. PostgreSQL
到了 1996 年，很明显“Postgres95”这个名字已经跟不上时代了。于是我们选择了一个新
名字PostgreSQL来反映与最初的POSTGRES和最新的具有SQL能力的版本之间的关系。同时版
本号也从 6.0 开始，将版本号放回到最初由伯克利POSTGRES项目开始的序列中。
很多人会因为传统或者更容易发音而继续用“Postgres”来指代PostgreSQL（现在很少用全
大写字母）。这种用法也被广泛接受为一种昵称或别名。
Postgres95的开发重点放在标识和理解后端代码的现有问题上。PostgreSQL的开发重点则转
到了一些有争议的特性和功能上面，当然各个方面的工作同时都在进行。
自那以来，PostgreSQL发生的变化可以在附录 E中找到。
约定
下面的约定被用于命令的大纲：方括弧（[和]）表示可选的部分（在 Tcl 命令里，使用的是
问号（?），就像通常的 Tcl 一样）。花括弧（{和}）和竖线（|）表示你必须选取一个候
选。点（…）表示它前面的元素可以被重复。
如果能提高清晰度，那么 SQL 命令前面会放上提示符=>，而 shell 命令前面会放上提示符
$。不过，提示符通常不被显示。
一个管理员通常是一个负责安装和运行服务器的人员。一个用户可以是任何使用或者需要
使用PostgreSQL系统的任何部分的人员。我们不应该对这些术语的概念理解得太狭隘；这
份文档集在系统管理过程方面没有固定的假设。
进一步的信息
除了本文档（即本书），还有其他关于PostgreSQL的资源：
维基
PostgreSQL wiki6包含项目的FAQ7（常见问题）列表、TODO8列表以及更多主题的详细信
息。
网站
PostgreSQL网站9中有最新发布的详细信息，以及其他让你能够以更高效使用PostgreSQL
的信息。
邮件列表
邮件列表是一个提问的好地方，也可以与其他用户分享经验，还可以用来联系开发者。
详情请参考PostgreSQL网站。
你自己！
PostgreSQL是一个开源项目。就其本身而言，它依赖于用户社区提供不间断的支持。如
果你刚开始使用PostgreSQL，你将需要从其他人那里得到帮助，或者通过文档或者通过
6
https://wiki.postgresql.org
7
https://wiki.postgresql.org/wiki/Frequently_Asked_Questions
8
https://wiki.postgresql.org/wiki/Todo
9
https://www.postgresql.org
xxxiii
前言
邮件列表。请考虑把你的知识贡献出来回馈社区。阅读邮件列表并且回答问题。如果你
学到某些不在文档中的东西，请把它写下来并且贡献出来。如果你为代码增加了特性，
也请把它们贡献出来。
缺陷报告指南
当你在PostgreSQL中找到一个缺陷时，我们希望听到关于它的事情。你的缺陷报告在使得
PostgreSQL更可靠的工作上扮演了重要的角色，因为再谨慎也不能保证PostgreSQL的每一部
分都能在每一个平台上、每一种环境中工作。
下列建议目的是协助你把缺陷形成便于高效处理的形式。没有人被要求遵循它们，但是这样
做对每个人都好。
我们无法保证马上修复每一个缺陷。如果缺陷是明显的、严重的或者影响很多用户，那么这
是个好机会让某人去检查它。也可能我们会告诉你升级到一个新的版本来查看缺陷是否在该
版本里也存在。或者我们可能决定在我们计划中的某些重大重写完成之前缺陷无法被修复。
或者也许就是修复该缺陷太困难并且在日程表上还有更多重要的事情要做。如果你需要立即
得到帮助，考虑联系一个商业支持。
5.1. 标识缺陷
在你报告一个缺陷之前，请阅读再阅读文档来验证你真地可以做你正在尝试的任何东西。如
果从文档中无法清楚地知道你是否能做某事，也请把它报告给我们；这是一个文档中的缺
陷。如果发现一个程序做的事情和文档说的不一样，那就是一个缺陷。那可能包括（但不限
于）下列情况：
• 一个程序带有一个致命信号或者一个操作系统错误消息终止，它们会指出程序中的一个问
题（一个反例是一个“磁盘满”消息，因为你只能自己修复它）。
• 一个程序对任何给定的输入产生错误的输出。
• 一个程序拒绝接受合法的输入（按文档定义）。
• 一个程序接受非法输入，并且没有提示或者错误消息。但是记住你对非法输入的概念可能
是我们认为的一个扩展或兼容性。
• PostgreSQL根据在被支持平台上的指导无法编译、建立或者安装。
这里的“程序”指任何可执行文件，不仅仅是后台进程。
很慢或者很占资源不一定是一个缺陷。阅读文档或者在邮件列表中寻求帮助来调优你的应
用。无法符合SQL标准也不一定是一个缺陷，除非文档中已经明确地声明指定特性是兼容
的。
在你继续之前，检查 TODO 列表和 FAQ 来看你的缺陷是否为已知。如果你不能理解 TODO 列
表中的信息，那就报告你的问题。至少我们可以让 TODO 列表更清晰。
5.2. 报告什么
关于缺陷报告要记住的最重要的事情是说明事实并且只说明事实。不要推断你觉得什么出错
了、“它看起来在做”什么或者程序的哪一部分出错了。如果你不熟悉实现，你可能猜错并
且不会帮到我们。而且即使你熟悉实现，受过训练的解释是巨大的补充但是也无法替代事
实。如果我们想要修复缺陷，我们还是需要首先重现它。报告最基本的事实相对直接（你可
以直接从屏幕上拷贝和粘贴它们）但是可能有太多重要的细节被略去，因为某些人可能认为
它不重要（所以没有包含在报告中）或者报告可能以其他方式被理解。
下列项应该被包含在每一个缺陷报告中：
• 从程序启动开始的准确步骤序列，我们需要它们来重现问题。这应该是自包含的，如果输
出是依赖于表中的数据，那么只发送一个裸的SELECT语句而不发送前面的CREATE
xxxiv
前言
TABLE和INSERT语句是不够的。我们没有时间来对你的数据库模式做逆向工程，而且即使
我们能够通过我们自己的数据来弥补，我们也很可能会错过问题。
SQL 相关问题的一个测试样例的最佳格式是一个可以通过psql前端运行并展示该问题的文
件（注意不要在你的~/.psqlrc启动文件中包含任何东西）。一种简单的创建该文件的方
法是使用pg_dump来转储出表声明和设置场景的数据，然后增加问题查询。我们鼓励你最
小化你的例子的尺寸，但是这不是绝对必要的。如果缺陷是可重现的，我们以两种方法都
可以找到它。
如果你的应用使用某些其他客户端接口（例如PHP），那么请尝试隔离出错的查询。我们
将可能无法设置一个网页服务器来重现你的问题。在任何情况中记住提供准确的输入文
件，不要猜测问题是因为“大文件” 或“中等大小的数据库”等而发生，因为这些信息
用起来太不准确。
• 你得到的输出。请不要说它“无法工作”或者“崩溃了”。如果有一个错误消息，请展示
它，即使你不理解它。如果程序带着一个操作系统错误而终止，请说出它。如果根本没有
发生任何事情，也说出来。虽然你的测试样例的结果是一次程序崩溃，但是显然它不一定
会在我们的平台上发生。如果可能的话，最简单的事情就是从终端上复制你的输出。
注意
如果你正在报告一个错误消息，请获得消息的最冗长的形式。在psql中，预
先执行\set VERBOSITY verbose。如果你在从服务器日志抽取消息，设置运
行时参数log_error_verbosity为verbose，这样所有的细节将被记录在日志
中。
注意
在致命错误的情况中，客户端报告的错误消息可能不会包含所有可用的信
息。请也看看数据库服务器的日志输出。如果你没有保留你的服务器的日志
输出，这将是一个最好的时机开始记录它。
• 你所期望的输出也是需要说明的很重要的内容。如果你只写“这个命令给我那个输
出。”或“这不是我所期待的。”，我们可能自己运行它、扫描输出并且认为它看起来
OK 并且正是我们期望的。我们不会花时间来理解你的命令背后的准确语义。特别是避免
仅仅说“这不是 SQL 所说的/Oracle 所作的。”从SQL中挖掘出正确的行为不是一个有趣
的工作，我们也没法去了解所有的其他关系型数据库的行为（如果你的问题是一个程序崩
溃，你显然可以忽略这一项）。
• 任何命令行选项和其他启动选项，包括任何相关的环境变量或者你从默认修改过的配置文
件。再次，请提供准确的信息。如果你在使用一个预打包的发布并且它在系统启动时自动
开始数据库服务器，你应当尝试找出它是怎样启动的。
• 任何你做的和安装指导上不同的地方。
• PostgreSQL的版本。你可以运行命令SELECT version();来找出你连接到的服务器的版
本。大部分可执行程序也支持一个–version选项，至少postgres --version和psql –
version可以工作。如果该函数或选项不存在，那么你的版本就已经老得无法保证可以升
级了。如果你运行的是一个预打包的版本（如 RPM），请说明并且包括该包的任何子版
本。如果你在谈论一个 Git 快照，也请说明并且包括提交哈希值。
如果你的版本比 12.2 更老，我们将几乎肯定会告诉你进行升级。在每一个新的发布中都
包含很多缺陷修复和改进，因此很有可能你在旧版本PostgreSQL中碰到的一个缺陷已经被
修复了。我们对使用旧版本PostgreSQL的站点只提供有限的支持。如果你需要更多支持，
请考虑咨询一个商业支持。
xxxv
前言
• 平台信息。这包括内核名称和版本、C 库、处理器、内存信息等等。在大部分情况中，提
供厂家和版本就足够了，但是不要假定每个人都了解“Debian”中到底包含什么或者每个
人都运行在 x86_64 上。如果你碰到的是安装问题，那么你机器上的工具链（编译
器、make等等）的信息也是需要被报告的。
不要担心你的缺陷报告变得太长。这就是生活。最好是第一次就报告所有的东西，而不是让
我们去从你那里询问。在另一方面，如果你的输入文件太大，最好先问问是否有人有兴趣去
看它。这里有一篇文章10勾勒了一些报告缺陷的提示。
不要把你所有的时间花费在指出输入中的哪些改变让问题消失。这可能对解决问题没有什么
帮助。如果发现缺陷不能被立马解决，你将还有时间去寻找和分享你的解决方法。同样，不
要浪费你的时间去猜测为什么缺陷会存在。我们将尽快找出原因。
在编写一份缺陷报告时，请避免使用含糊的术语。这个软件包从整体上被称
为“PostgreSQL”，有时会简称为“Postgres”。如果你要谈论特定的后端进程，不要只
说“PostgreSQL 崩溃了”。一个单一后端进程的崩溃和其父“postgres”进程崩溃是完全
不同的；当你想说一个单一后端进程垮掉时，请不要说“服务器崩溃了”，反之亦然。还
有，如交互式前端“psql”等的客户端程序和后端是完全独立的。请尽量确定问题是发生在
客户端还是服务器端。
5.3. 向哪里报告缺陷
通常，请把缺陷报告发送到缺陷报告邮件列表pgsql-bugs@lists.postgresql.org。你会
被要求给你的电子邮件消息加上一个描述性的主题，可以用错误消息的一部分。
另一种方法是填写项目网站11上的缺陷报告网页表格。以这种方式输入的缺陷报告会被发送
到pgsql-bugs@lists.postgresql.org邮件列表。
如果你的缺陷报告牵涉到安全并且你不想让它立刻变得公众可见，不要把它发送到pgsqlbugs。安全问题可以被私下报告给security@lists.postgresql.org。
不要将缺陷报告发送到任何一个用户邮件列表，例
如pgsql-sql@lists.postgresql.org或pgsql-general@lists.postgresql.org。这些邮
件列表是用来回答用户问题的，并且它们的订阅者通常不希望收到缺陷报告。更重要的是，
他们不可能去修复缺陷。
另外，请不要把报告发送给开发者邮件列表pgsql-hackers@lists.postgresql.org。这个
列表是用来讨论PostgreSQL开发的地方，并且把缺陷报告隔离开对我们会比较好。如果问题
需要更多的审查，我们可能选择在pgsql-hackers上对你的缺陷报告展开一次讨论。
如果你有一个关于文档的问题，报告它最好的地方是文档邮件列
表pgsql-docs@postgresql.org。请指出你对文档的哪个部分不爽。
如果你的缺陷是一个在非被支持平台上的移植性问题，请发送邮件
到pgsql-hackers@postgresql.org，这样我们（和你）就可以做些工作把PostgreSQL移植
到你的平台。
注意
为了防止出现大量的垃圾邮件，除非您订阅，否则上述所有列表都将被审核。
这意味着在邮件被发送之前，将有一些延迟。如果您想订阅列表，请访
问https://lists.postgresql.org/获取说明。
10 https://www.chiark.greenend.org.uk/~sgtatham/bugs.html
11 https://www.postgresql.org/
xxxvi
部分 I. 教程
欢迎来到PostgreSQL教程。下面的几章将为那些新接触PostgreSQL、关系数据库概念和 SQL 语言的读
者给出一个简单介绍。我们只假定读者拥有关于如何使用计算机的一般知识。读者不需要特殊的 Unix
或编程经验。这一部分主要希望给你一些关于PostgreSQL系统的重要方面的手把手的体验。我们并不准
备把它写成一份能覆盖这些主题所有内容的文档。
当你完成了这份教程之后，你可能希望继续去阅读第 II 部分来得到 SQL 语言更正式的知识，或者
阅读第 IV 部分来了解如何开发PostgreSQL应用程序。希望搭建并且管理自己的服务器的用户还应该阅
读第 III 部分。
目录
从头开始 … 3
1.1. 安装 … 3
1.2. 架构基础 … 3
1.3. 创建一个数据库 … 3
1.4. 访问数据库 … 5
SQL语言 … 7
2.1. 引言 … 7
2.2. 概念 … 7
2.3. 创建一个新表 … 7
2.4. 在表中增加行 … 8
2.5. 查询一个表 … 9
2.6. 在表之间连接 … 10
2.7. 聚集函数 … 13
2.8. 更新 … 14
2.9. 删除 … 14
高级特性 … 16
3.1. 简介 … 16
3.2. 视图 … 16
3.3. 外键 … 16
3.4. 事务 … 17
3.5. 窗口函数 … 18
3.6. 继承 … 21
3.7. 小结 … 22
2
第 1 章从头开始
1.1. 安装
自然，在你能开始使用PostgreSQL之前，你必须安装它。PostgreSQL很有可能已经安装到
你的节点上了，因为它可能包含在你的操作系统的发布里，或者是系统管理员已经安装了
它。如果是这样的话，那么你应该从操作系统的文档或者你的系统管理员那里获取有关如
何访问PostgreSQL的信息。
如果你不清楚PostgreSQL是否已经安装，或者不知道你能否用它（已经安装的）做自己的
实验，那么你就可以自己安装。这么做并不难，并且是一次很好的练习。PostgreSQL可以
由任何非特权用户安装，并不需要超级用户（root）的权限。
如果你准备自己安装PostgreSQL，那么请参考第 16 章以获取安装的有关信息，安装之后
再回到这个指导手册来。一定要记住要尽可能遵循有关设置合适的环境变量章节里的信息。
如果你的站点管理员没有按照缺省的方式设置各项相关参数，那你还有点额外的活儿要
干。比如，如果数据库服务器机器是一个远程的机器，那你就需要把PGHOST环境变量设置
为数据库服务器的名字。环境变量PGPORT也可能需要设置。总而言之就是：如果当你试着
启动一个应用而该应用报告说不能与数据库建立联接时，你应该马上与你的数据库管理员
联系，如果你就是管理员，那么你就要参考文档以确保你的环境变量得到正确的设置。如
果你不理解随后的几段，那么先阅读下一节。
1.2. 架构基础
在我们继续之前，你应该先了解PostgreSQL的系统架构。对PostgreSQL的部件之间如何相
互作用的理解将会使本节更易理解。
在数据库术语里，PostgreSQL使用一种客户端/服务器的模型。一次PostgreSQL会话由下列
相关的进程（程序）组成：
• 一个服务器进程，它管理数据库文件、接受来自客户端应用与数据库的联接并且代表客户
端在数据库上执行操作。该数据库服务器程序叫做postgres。
• 那些需要执行数据库操作的用户的客户端（前端）应用。客户端应用可能本身就是多种
多样的：可以是一个面向文本的工具，也可以是一个图形界面的应用，或者是一个通过
访问数据库来显示网页的网页服务器，或者是一个特制的数据库管理工具。一些客户端
应用是和 PostgreSQL发布一起提供的，但绝大部分是用户开发的。
和典型的客户端/服务器应用（C/S应用）一样，这些客户端和服务器可以在不同的主机上。
这时它们通过 TCP/IP 网络联接通讯。你应该记住的是，在客户机上可以访问的文件未必能
够在数据库服务器机器上访问（或者只能用不同的文件名进行访问）。
PostgreSQL服务器可以处理来自客户端的多个并发请求。因此，它为每个连接启动
（“forks”）一个新的进程。从这个时候开始，客户端和新服务器进程就不再经过最初
的 postgres进程的干涉进行通讯。因此，主服务器进程总是在运行并等待着客户端联
接，而客户端和相关联的服务器进程则是起起停停（当然，这些对用户是透明的。我们介
绍这些主要是为了内容的完整性）。
1.3. 创建一个数据库
看看你能否访问数据库服务器的第一个例子就是试着创建一个数据库。一台运行着的
PostgreSQL服务器可以管理许多数据库。通常我们会为每个项目和每个用户单独使用一个
数据库。
你的站点管理员可能已经为你创建了可以使用的数据库。如果这样你就可以省略这一步，
并且跳到下一节。
3
从头开始
要创建一个新的数据库，在我们这个例子里叫mydb，你可以使用下面的命令：
$ createdb mydb
如果不产生任何响应则表示该步骤成功，你可以跳过本节的剩余部分。
如果你看到类似下面这样的信息：
createdb: command not found
那么就是PostgreSQL没有安装好。或者是根本没安装，或者是你的shell搜索路径没有设置
正确。尝试用绝对路径调用该命令试试：
$ /usr/local/pgsql/bin/createdb mydb
在你的站点上这个路径可能不一样。和你的站点管理员联系或者看看安装指导获取正确的位
置。
另外一种响应可能是这样：
createdb: could not connect to database postgres: could not connect to server:
No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/tmp/.s.PGSQL.5432”?
这意味着该服务器没有启动，或者没有按照createdb预期地启动。同样，你也要查看安装
指导或者咨询管理员。
另外一个响应可能是这样：
createdb: could not connect to database postgres: FATAL: role “joe” does not
exist
在这里提到了你自己的登录名。如果管理员没有为你创建PostgreSQL用户帐号，就会发生
这些现象。（PostgreSQL用户帐号和操作系统用户帐号是不同的。）如果你是管理员，参
阅第 21 章获取创建用户帐号的帮助。你需要变成安装PostgreSQL的操作系统用户的身份
（通常是 postgres）才能创建第一个用户帐号。也有可能是赋予你的PostgreSQL用户名和
你的操作系统用户名不同；这种情况下，你需要使用-U选项或者使用PGUSER环境变量指定
你的PostgreSQL用户名。
如果你有个数据库用户帐号，但是没有创建数据库所需要的权限，那么你会看到下面的信
息：
createdb: database creation failed: ERROR: permission denied to create database
并非所有用户都被许可创建新数据库。如果PostgreSQL拒绝为你创建数据库，那么你需要
让站点管理员赋予你创建数据库的权限。出现这种情况时请咨询你的站点管理员。如果你
自己安装了PostgreSQL，那么你应该以你启动数据库服务器的用户身份登录然后参考手册
完成权限的赋予工作。 1
你还可以用其它名字创建数据库。PostgreSQL允许你在一个站点上创建任意数量的数据库。
数据库名必须是以字母开头并且小于 63 个字符长。一个方便的做法是创建和你当前用户名
1
为什么这么做的解释：PostgreSQL用户名是和操作系统用户账号分开的。如果你连接到一个数据库时，你可以选择以何种
PostgreSQL用户名进行联接；如果你不选择，那么缺省就是你的当前操作系统账号。如果这样，那么总有一个与操作系统用户
同名的PostgreSQL用户账号用于启动服务器，并且通常这个用户都有创建数据库的权限。如果你不想以该用户身份登录，那么
你也可以在任何地方声明一个-U选项以选择一个用于连接的PostgreSQL用户名。
4
从头开始
同名的数据库。许多工具假设该数据库名为缺省数据库名，所以这样可以节省你的敲键。
要创建这样的数据库，只需要键入：
$ createdb
如果你再也不想使用你的数据库了，那么你可以删除它。比如，如果你是数据库mydb的所
有人（创建人），那么你就可以用下面的命令删除它：
$ dropdb mydb
（对于这条命令而言，数据库名不是缺省的用户名，因此你就必须声明它）。这个动作将
在物理上把所有与该数据库相关的文件都删除并且不可取消，因此做这中操作之前一定要
考虑清楚。
更多关于createdb和dropdb的信息可以分别在createdb和dropdb中找到。
1.4. 访问数据库
一旦你创建了数据库，你就可以通过以下方式访问它：
• 运行PostgreSQL的交互式终端程序，它被称为psql，它允许你交互地输入、编辑和执行
SQL命令。
• 使用一种已有的图形化前端工具，比如pgAdmin或者带ODBC或JDBC支持的办公套件来创建
和管理数据库。这种方法在这份教程中没有介绍。
• 使用多种绑定发行的语言中的一种写一个自定义的应用。这些可能性在第 IV 部分中将有
更深入的讨论。
你可能需要启动psql来试验本教程中的例子。你可以用下面的命令为mydb数据库激活它：
$ psql mydb
如果你不提供数据库名字，那么它的缺省值就是你的用户账号名字。在前面使用createdb的
小节里你应该已经了解了这种方式。
在psql中，你将看到下面的欢迎信息：
psql (12.2)
Type “help” for help.
mydb=>
最后一行也可能是：
mydb=#
这个提示符意味着你是数据库超级用户，最可能出现在你自己安装了 PostgreSQL实例的情
况下。作为超级用户意味着你不受访问控制的限制。对于本教程的目的而言，是否超级用
户并不重要。
如果你启动psql时碰到了问题，那么请回到前面的小节。诊断createdb的方法和诊断
psql的方法很类似，如果前者能运行那么后者也应该能运行。
psql打印出的最后一行是提示符，它表示psql正听着你说话，这个时候你就可以敲入 SQL查
询到一个psql维护的工作区中。试验一下下面的命令：
5
从头开始
mydb=> SELECT version();
version

PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10)
4.9.2, 64-bit
(1 row)
mydb=> SELECT current_date;
date

2016-01-07
(1 row)
mydb=> SELECT 2 + 2;
?column?

4
(1 row)
psql程序有一些不属于SQL命令的内部命令。它们以反斜线开头，“\”。欢迎信息中列出
了一些这种命令。比如，你可以用下面的命令获取各种PostgreSQL的SQL命令的帮助语法：
mydb=> \h
要退出psql，输入：
mydb=> \q
psql将会退出并且让你返回到命令行shell。（要获取更多有关内部命令的信息，你可以
在psql提示符上键入?。） psql的完整功能在psql中有文档说明。在这份文档里，我们将
不会明确使用这些特性，但是你自己可以在需要的时候使用它们。
6
第 2 章 SQL语言
2.1. 引言
本章提供一个如何使用SQL执行简单操作的概述。本教程的目的只是给你一个介绍，并非完
整的SQL教程。有许多关于SQL的书籍，包括[melt93]和[date97]。你还要知道有些
PostgreSQL语言特性是对标准的扩展。
在随后的例子里，我们假设你已经创建了名为mydb的数据库，就象在前面的章里面介绍的一
样，并且已经能够启动psql。
本手册的例子也可以在PostgreSQL源代码的目录src/tutorial/中找到（二进制PostgreSQL
发布中可能没有编译这些文件）。要使用这些文件，首先进入该目录然后运行make：
$ cd …/src/tutorial
$ make
这样就创建了那些脚本并编译了包含用户定义函数和类型的 C 文件。接下来，要开始本教
程，按照下面说的做：
$ cd …/tutorial
$ psql -s mydb
…
mydb=> \i basics.sql
\i命令从指定的文件中读取命令。psql的-s选项把你置于单步模式，它在向服务器发送每个
语句之前暂停。在本节使用的命令都在文件basics.sql中。
2.2. 概念
PostgreSQL是一种关系型数据库管理系统（RDBMS）。这意味着它是一种用于管理存储
在关系中的数据的系统。关系实际上是表的数学术语。今天，把数据存储在表里的概念已
经快成了固有的常识了，但是还有其它的一些方法用于组织数据库。在类 Unix 操作系统上
的文件和目录就形成了一种层次数据库的例子。更现代的发展是面向对象数据库。
每个表都是一个命名的行集合。一个给定表的每一行由同一组的命名列组成，而且每一列
都有一个特定的数据类型。虽然列在每行里的顺序是固定的，但一定要记住 SQL 并不对行
在表中的顺序做任何保证（但你可以为了显示的目的对它们进行显式地排序）。
表被分组成数据库，一个由单个PostgreSQL服务器实例管理的数据库集合组成一个数据
库集簇。
2.3. 创建一个新表
你可以通过指定表的名字和所有列的名字及其类型来创建表∶
CREATE TABLE weather (
city varchar(80),
temp_lo int, – 最低温度
temp_hi int, – 最高温度
7
SQL语言
prcp real, – 湿度
date date
);
你可以在psql输入这些命令以及换行符。psql可以识别该命令直到分号才结束。
你可以在 SQL 命令中自由使用空白（即空格、制表符和换行符）。这就意味着你可以用和
上面不同的对齐方式键入命令，或者将命令全部放在一行中。两个划线（“–”）引入注
释。任何跟在它后面直到行尾的东西都会被忽略。SQL 是对关键字和标识符大小写不敏感的
语言，只有在标识符用双引号包围时才能保留它们的大小写（上例没有这么做）。
varchar(80)指定了一个可以存储最长 80 个字符的任意字符串的数据类型。int是普通的整
数类型。real是一种用于存储单精度浮点数的类型。date类型应该可以自解释（没错，类型
为date的列名字也是date。这么做可能比较方便或者容易让人混淆 — 你自己选择）。
PostgreSQL支持标准的SQL类型int、smallint、real、double
precision、char(N)、varchar(N)、date、time、timestamp和interval，还支持其他的通
用功能的类型和丰富的几何类型。PostgreSQL中可以定制任意数量的用户定义数据类型。因
而类型名并不是语法关键字，除了SQL标准要求支持的特例外。
第二个例子将保存城市和它们相关的地理位置：
CREATE TABLE cities (
name varchar(80),
location point
);
类型point就是一种PostgreSQL特有数据类型的例子。
最后，我们还要提到如果你不再需要某个表，或者你想以不同的形式重建它，那么你可以
用下面的命令删除它：
DROP TABLE tablename;
2.4. 在表中增加行
INSERT语句用于向表中添加行：
INSERT INTO weather VALUES (‘San Francisco’, 46, 50, 0.25, ‘1994-11-27’);
请注意所有数据类型都使用了相当明了的输入格式。那些不是简单数字值的常量通常必需用
单引号（'）包围，就象在例子里一样。date类型实际上对可接收的格式相当灵活，不过在
本教程里，我们应该坚持使用这种清晰的格式。
point类型要求一个座标对作为输入，如下：
INSERT INTO cities VALUES (‘San Francisco’, ‘(-194.0, 53.0)’);
到目前为止使用的语法要求你记住列的顺序。一个可选的语法允许你明确地列出列：
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES (‘San Francisco’, 43, 57, 0.0, ‘1994-11-29’);
如果你需要，你可以用另外一个顺序列出列或者是忽略某些列，比如说，我们不知道降水
量：
8
SQL语言
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES (‘1994-11-29’, ‘Hayward’, 54, 37);
许多开发人员认为明确列出列要比依赖隐含的顺序是更好的风格。
请输入上面显示的所有命令，这样你在随后的各节中才有可用的数据。
你还可以使用COPY从文本文件中装载大量数据。这种方式通常更快，因为COPY命令就是为
这类应用优化的，只是比 INSERT少一些灵活性。比如：
COPY weather FROM ‘/home/user/weather.txt’;
这里源文件的文件名必须在运行后端进程的机器上是可用的，而不是在客户端上，因为后
端进程将直接读取该文件。你可以在COPY中读到更多有关COPY命令的信息。
2.5. 查询一个表
要从一个表中检索数据就是查询这个表。SQL的SELECT语句就是做这个用途
的。该语句分为选择列表（列出要返回的列）、表列表（列出从中检索数据的表）以及可
选的条件（指定任意的限制）。比如，要检索表weather的所有行，键入：
SELECT * FROM weather;
这里是“所有列”的缩写。 1
因此相同的结果应该这样获得：
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
而输出应该是：
city | temp_lo | temp_hi | prcp | date
---------------±--------±--------±-----±-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
你可以在选择列表中写任意表达式，而不仅仅是列的列表。比如，你可以：
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
这样应该得到：
city | temp_avg | date
---------------±---------±-----------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)
请注意这里的AS子句是如何给输出列重新命名的（AS子句是可选的）。
1
虽然SELECT 对于即席查询很有用，但我们普遍认为在生产代码中这是很糟糕的风格，因为给表增加一个列就改变了结果。
9
SQL语言
一个查询可以使用WHERE子句“修饰”，它指定需要哪些行。WHERE子句包含一个布尔（真
值）表达式，只有那些使布尔表达式为真的行才会被返回。在条件中可以使用常用的布尔操
作符（AND、OR和NOT）。比如，下面的查询检索旧金山的下雨天的天气：
SELECT * FROM weather
WHERE city = ‘San Francisco’ AND prcp > 0.0;
结果：
city | temp_lo | temp_hi | prcp | date
---------------±--------±--------±-----±-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)
你可以要求返回的查询结果是排好序的：
SELECT * FROM weather
ORDER BY city;
city | temp_lo | temp_hi | prcp | date
---------------±--------±--------±-----±-----------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27
在这个例子里，排序的顺序并未完全被指定，因此你可能看到属于旧金山的行被随机地排
序。但是如果你使用下面的语句，那么就总是会得到上面的结果：
SELECT * FROM weather
ORDER BY city, temp_lo;
你可以要求在查询的结果中消除重复的行：
SELECT DISTINCT city
FROM weather;
city

Hayward
San Francisco
(2 rows)
再次声明，结果行的顺序可能变化。你可以组合使用DISTINCT和ORDER BY来保证获取一致的
结果： 2
SELECT DISTINCT city
FROM weather
ORDER BY city;
2.6. 在表之间连接
2
在一些数据库系统里，包括老版本的PostgreSQL，DISTINCT的实现自动对行进行排序，因此ORDER BY是多余的。但是这一点并
不是 SQL 标准的要求，并且目前的PostgreSQL并不保证DISTINCT会导致行被排序。
10
SQL语言
到目前为止，我们的查询一次只访问一个表。查询可以一次访问多个表，或者用这种方式访
问一个表而同时处理该表的多个行。一个同时访问同一个或者不同表的多个行的查询叫连
接查询。举例来说，比如你想列出所有天气记录以及相关的城市位置。要实现这个目标，我
们需要拿 weather表每行的city列和cities表所有行的name列进行比较，并选取那些在该值
上相匹配的行对。
注意
这里只是一个概念上的模型。连接通常以比实际比较每个可能的行对更高效的
方式执行，但这些是用户看不到的。
这个任务可以用下面的查询来实现：
SELECT *
FROM weather, cities
WHERE city = name;
city | temp_lo | temp_hi | prcp | date | name |
location
---------------±--------±--------±-----±-----------±--------------
±----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco |
(-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco |
(-194,53)
(2 rows)
观察结果集的两个方面：
• 没有城市Hayward的结果行。这是因为在cities表里面没有Hayward的匹配行，所以连接忽
略 weather表里的不匹配行。我们稍后将看到如何修补它。
• 有两个列包含城市名字。这是正确的，因为weather和cities表的列被串接在一起。不
过，实际上我们不想要这些，因此你将可能希望明确列出输出列而不是使用：
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
练习：. 看看这个查询省略WHERE子句的语义是什么
因为这些列的名字都不一样，所以规划器自动地找出它们属于哪个表。如果在两个表里有重
名的列，你需要限定列名来说明你究竟想要哪一个，如：
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;
人们广泛认为在一个连接查询中限定所有列名是一种好的风格，这样即使未来向其中一个表
里添加重名列也不会导致查询失败。
到目前为止，这种类型的连接查询也可以用下面这样的形式写出来：
11
SQL语言
SELECT
FROM weather INNER JOIN cities ON (weather.city = cities.name);
这个语法并不象上文的那个那么常用，我们在这里写出来是为了让你更容易了解后面的主
题。
现在我们将看看如何能把Hayward记录找回来。我们想让查询干的事是扫描weather表，并
且对每一行都找出匹配的cities表行。如果我们没有找到匹配的行，那么我们需要一些“空
值”代替cities表的列。这种类型的查询叫外连接（我们在此之前看到的连接都是内连
接）。这样的命令看起来象这样：
SELECT *
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
city | temp_lo | temp_hi | prcp | date | name |
location
---------------±--------±--------±-----±-----------±--------------
±----------
Hayward | 37 | 54 | | 1994-11-29 | |
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco |
(-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco |
(-194,53)
(3 rows)
这个查询是一个左外连接，因为在连接操作符左部的表中的行在输出中至少要出现一次，
而在右部的表的行只有在能找到匹配的左部表行时才被输出。如果输出的左部表的行没有
对应匹配的右部表的行，那么右部表行的列将填充空值（null）。
练习：. 还有右外连接和全外连接。试着找出来它们能干什么。
我们也可以把一个表和自己连接起来。这叫做自连接。比如，假设我们想找
出那些在其它天气记录的温度范围之外的天气记录。这样我们就需要拿 weather表里每行
的temp_lo和temp_hi列与weather表里其它行的temp_lo和temp_hi列进行比较。我们可以用
下面的查询实现这个目标：
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
W2.city, W2.temp_lo AS low, W2.temp_hi AS high
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;
city | low | high | city | low | high
---------------±----±-----±--------------±----±-----
San Francisco | 43 | 57 | San Francisco | 46 | 50
Hayward | 37 | 54 | San Francisco | 46 | 50
(2 rows)
在这里我们把weather表重新标记为W1和W2以区分连接的左部和右部。你还可以用这样的别
名在其它查询里节约一些敲键，比如：
SELECT *
FROM weather w, cities c
WHERE w.city = c.name;
你以后会经常碰到这样的缩写的。
12
SQL语言
2.7. 聚集函数
和大多数其它关系数据库产品一样，PostgreSQL支持聚集函数。一个聚集函数从多个输入
行中计算出一个结果。比如，我们有在一个行集合上计算count（计
数）、sum（和）、avg（均值）、max（最大值）和min（最小值）的函数。
比如，我们可以用下面的语句找出所有记录中最低温度中的最高温度：
SELECT max(temp_lo) FROM weather;
max

46
(1 row)
如果我们想知道该读数发生在哪个城市，我们可以用：
SELECT city FROM weather WHERE temp_lo = max(temp_lo); 错误
不过这个方法不能运转，因为聚集max不能被用于WHERE子句中（存在这个限制是因
为WHERE子句决定哪些行可以被聚集计算包括；因此显然它必需在聚集函数之前被计算）。
不过，我们通常都可以用其它方法实现我们的目的；这里我们就可以使用子查询：
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city

San Francisco
(1 row)
这样做是 OK 的，因为子查询是一次独立的计算，它独立于外层的查询计算出自己的聚集。
聚集同样也常用于和GROUP BY子句组合。比如，我们可以获取每个城市观测到的最低温度
的最高值：
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
city | max
---------------±----
Hayward | 37
San Francisco | 46
(2 rows)
这样给我们每个城市一个输出。每个聚集结果都是在匹配该城市的表行上面计算的。我们可
以用HAVING 过滤这些被分组的行：
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
13
SQL语言
HAVING max(temp_lo) < 40;
city | max
---------±----
Hayward | 37
(1 row)
这样就只给出那些所有temp_lo值曾都低于 40的城市。最后，如果我们只关心那些名字
以“S”开头的城市，我们可以用：
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE ‘S%’ – 1
GROUP BY city
HAVING max(temp_lo) < 40;
1 LIKE操作符进行模式匹配，在第 9.7 节里有解释。
理解聚集和SQL的WHERE以及HAVING子句之间的关系对我们非常重要。WHERE和HAVING的基本
区别如下：WHERE在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计
算），而HAVING在分组和聚集之后选取分组行。因此，WHERE子句不能包含聚集函
数；因为试图用聚集函数判断哪些行应输入给聚集运算是没有意义的。相反，HAVING子句
总是包含聚集函数（严格说来，你可以写不使用聚集的HAVING子句，但这样做很少有用。
同样的条件用在WHERE阶段会更有效）。
在前面的例子里，我们可以在WHERE里应用城市名称限制，因为它不需要聚集。这样比放
在HAVING里更加高效，因为可以避免那些未通过 WHERE检查的行参与到分组和聚集计算中。
2.8. 更新
你可以用UPDATE命令更新现有的行。假设你发现所有 11 月 28 日以后的温度读数都低了两
度，那么你就可以用下面的方式改正数据：
UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > ‘1994-11-28’;
看看数据的新状态：
SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------±--------±--------±-----±-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29
(3 rows)
2.9. 删除
数据行可以用DELETE命令从表中删除。假设你对Hayward的天气不再感兴趣，那么你可以用
下面的方法把那些行从表中删除：
14
SQL语言
DELETE FROM weather WHERE city = ‘Hayward’;
所有属于Hayward的天气记录都被删除。
SELECT * FROM weather;
city | temp_lo | temp_hi | prcp | date
---------------±--------±--------±-----±-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
(2 rows)
我们用下面形式的语句的时候一定要小心
DELETE FROM tablename;
如果没有一个限制，DELETE将从指定表中删除所有行，把它清空。做这些之前系统不会请求
你确认！
15
第 3 章高级特性
3.1. 简介
在之前的章节里我们已经涉及了使用SQL在PostgreSQL中存储和访问数据的基础知识。现在
我们将要讨论SQL中一些更高级的特性，这些特性有助于简化管理和防止数据丢失或损坏。
最后，我们还将介绍一些PostgreSQL扩展。
本章有时将引用第 2 章中的例子并对其进行改变或改进以便于阅读本章。本章中的某些例子
可以在教程目录的advanced.sql文件中找到。该文件也包含一些样例数据，在这里就不在赘
述（查看第 2.1 节了解如何使用该文件）。
3.2. 视图
回想一下第 2.6 节中的查询。假设天气记录和城市位置的组合列表对我们的应用有用，但我
们又不想每次需要使用它时都敲入整个查询。我们可以在该查询上创建一个视图，这会给该
查询一个名字，我们可以像使用一个普通表一样来使用它：
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
对视图的使用是成就一个好的SQL数据库设计的关键方面。视图允许用户通过始终如一的接
口封装表的结构细节，这样可以避免表结构随着应用的进化而改变。
视图几乎可以用在任何可以使用表的地方。在其他视图基础上创建视图也并不少见。
3.3. 外键
回想第2章中的weather和cities表。考虑以下问题：我们希望确保在cities表中有相应项之
前任何人都不能在weather表中插入行。这叫做维持数据的引用完整性。在过分简化的数据
库系统中，可以通过先检查cities表中是否有匹配的记录存在，然后决定应该接受还是拒绝
即将插入weather表的行。这种方法有一些问题且并不方便，于是PostgreSQL可以为我们来
解决：
新的表定义如下：
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
现在尝试插入一个非法的记录：
16
高级特性
INSERT INTO weather VALUES (‘Berkeley’, 45, 53, 0.0, ‘1994-11-28’);
ERROR: insert or update on table “weather” violates foreign key constraint
“weather_city_fkey”
DETAIL: Key (city)=(Berkeley) is not present in table “cities”.
外键的行为可以很好地根据应用来调整。我们不会在这个教程里更深入地介绍，读者可以参
考第 5 章中的信息。正确使用外键无疑会提高数据库应用的质量，因此强烈建议用户学会如
何使用它们。
3.4. 事务
事务是所有数据库系统的基础概念。事务最重要的一点是它将多个步骤捆绑成了一个单一
的、要么全完成要么全不完成的操作。步骤之间的中间状态对于其他并发事务是不可见的，
并且如果有某些错误发生导致事务不能完成，则其中任何一个步骤都不会对数据库造成影
响。
例如，考虑一个保存着多个客户账户余额和支行总存款额的银行数据库。假设我们希望记录
一笔从Alice的账户到Bob的账户的额度为100.00美元的转账。在最大程度地简化后，涉及到
的SQL命令是：
UPDATE accounts SET balance = balance - 100.00
WHERE name = ‘Alice’;
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = ‘Alice’);
UPDATE accounts SET balance = balance + 100.00
WHERE name = ‘Bob’;
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = ‘Bob’);
这些命令的细节在这里并不重要，关键点是为了完成这个相当简单的操作涉及到多个独立的
更新。我们的银行职员希望确保这些更新要么全部发生，或者全部不发生。当然不能发生因
为系统错误导致Bob收到100美元而Alice并未被扣款的情况。Alice当然也不希望自己被扣款
而Bob没有收到钱。我们需要一种保障，当操作中途某些错误发生时已经执行的步骤不会产
生效果。将这些更新组织成一个事务就可以给我们这种保障。一个事务被称为是原子的：从
其他事务的角度来看，它要么整个发生要么完全不发生。
我们同样希望能保证一旦一个事务被数据库系统完成并认可，它就被永久地记录下来且即便
其后发生崩溃也不会被丢失。例如，如果我们正在记录Bob的一次现金提款，我们当然不希
望他刚走出银行大门，对他账户的扣款就消失。一个事务型数据库保证一个事务在被报告为
完成之前它所做的所有更新都被记录在持久存储（即磁盘）。
事务型数据库的另一个重要性质与原子更新的概念紧密相关：当多个事务并发运行时，每一
个都不能看到其他事务未完成的修改。例如，如果一个事务正忙着总计所有支行的余额，它
不会只包括Alice的支行的扣款而不包括Bob的支行的存款，或者反之。所以事务的全做或全
不做并不只体现在它们对数据库的持久影响，也体现在它们发生时的可见性。一个事务所做
的更新在它完成之前对于其他事务是不可见的，而之后所有的更新将同时变得可见。
在PostgreSQL中，开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来。因此我们的
银行事务看起来会是这样：
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = ‘Alice’;
– etc etc
17
高级特性
COMMIT;
如果，在事务执行中我们并不想提交（或许是我们注意到Alice的余额不足），我们可以发
出ROLLBACK命令而不是COMMIT命令，这样所有目前的更新将会被取消。
PostgreSQL实际上将每一个SQL语句都作为一个事务来执行。如果我们没有发出BEGIN命令，
则每个独立的语句都会被加上一个隐式的BEGIN以及（如果成功）COMMIT来包围它。一组
被BEGIN和COMMIT包围的语句也被称为一个事务块。
注意
某些客户端库会自动发出BEGIN和COMMIT命令，因此我们可能会在不被告知的
情况下得到事务块的效果。具体请查看所使用的接口文档。
也可以利用保存点来以更细的粒度来控制一个事务中的语句。保存点允许我们有选择性地放
弃事务的一部分而提交剩下的部分。在使用SAVEPOINT定义一个保存点后，我们可以在必要
时利用ROLLBACK TO回滚到该保存点。该事务中位于保存点和回滚点之间的数据库修改都会
被放弃，但是早于该保存点的修改则会被保存。
在回滚到保存点之后，它的定义依然存在，因此我们可以多次回滚到它。反过来，如果确定
不再需要回滚到特定的保存点，它可以被释放以便系统释放一些资源。记住不管是释放保存
点还是回滚到保存点都会释放定义在该保存点之后的所有其他保存点。
所有这些都发生在一个事务块内，因此这些对于其他数据库会话都不可见。当提交整个事务
块时，被提交的动作将作为一个单元变得对其他会话可见，而被回滚的动作则永远不会变得
可见。
记住那个银行数据库，假设我们从Alice的账户扣款100美元，然后存款到Bob的账户，结果
直到最后才发现我们应该存到Wally的账户。我们可以通过使用保存点来做这件事：
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = ‘Alice’;
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = ‘Bob’;
– oops … forget that and use Wally’s account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = ‘Wally’;
COMMIT;
当然，这个例子是被过度简化的，但是在一个事务块中使用保存点存在很多种控制可能性。
此外，ROLLBACK TO是唯一的途径来重新控制一个由于错误被系统置为中断状态的事务块，
而不是完全回滚它并重新启动。
3.5. 窗口函数
一个窗口函数在一系列与当前行有某种关联的表行上执行一种计算。这与一个聚集函数所完
成的计算有可比之处。但是窗口函数并不会使多行被聚集成一个单独的输出行，这与通常的
非窗口聚集函数不同。取而代之，行保留它们独立的标识。在这些现象背后，窗口函数可以
访问的不仅仅是查询结果的当前行。
下面是一个例子用于展示如何将每一个员工的薪水与他/她所在部门的平均薪水进行比较：
18
高级特性
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM
empsalary;
depname | empno | salary | avg
-----------±------±-------±----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
最开始的三个输出列直接来自于表empsalary，并且表中每一行都有一个输出行。第四列表
示对与当前行具有相同depname值的所有表行取得平均值（这实际和非窗口avg聚集函数是相
同的函数，但是OVER子句使得它被当做一个窗口函数处理并在一个合适的窗口帧上计
算。）。
一个窗口函数调用总是包含一个直接跟在窗口函数名及其参数之后的OVER子句。这使得它从
句法上和一个普通函数或非窗口函数区分开来。OVER子句决定究竟查询中的哪些行被分离出
来由窗口函数处理。OVER子句中的PARTITION BY子句指定了将具有相同PARTITION BY表达式
值的行分到组或者分区。对于每一行，窗口函数都会在当前行同一分区的行上进行计算。
我们可以通过OVER上的ORDER BY控制窗口函数处理行的顺序（窗口的ORDER BY并不一定要符
合行输出的顺序。）。下面是一个例子：
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
depname | empno | salary | rank
-----------±------±-------±-----
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)
如上所示，rank函数在当前行的分区内按照ORDER BY子句的顺序为每一个可区分的ORDER
BY值产生了一个数字等级。rank不需要显式的参数，因为它的行为完全决定于OVER子句。
一个窗口函数所考虑的行属于那些通过查询的FROM子句产生并通过WHERE、GROUP
BY、HAVING过滤的“虚拟表”。例如，一个由于不满足WHERE条件被删除的行是不会被任何
窗口函数所见的。在一个查询中可以包含多个窗口函数，每个窗口函数都可以用不同
的OVER子句来按不同方式划分数据，但是它们都作用在由虚拟表定义的同一个行集上。
我们已经看到如果行的顺序不重要时ORDER BY可以忽略。PARTITION BY同样也可以被忽略，
在这种情况下会产生一个包含所有行的分区。
19
高级特性
这里有一个与窗口函数相关的重要概念：对于每一行，在它的分区中的行集被称为它的窗口
帧。一些窗口函数只作用在窗口帧中的行上，而不是整个分区。默认情况下，如果使
用ORDER BY，则帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子
句上相等的行。如果ORDER BY被忽略，则默认帧包含整个分区中所有的行。 1
下面是使
用sum的例子：
SELECT salary, sum(salary) OVER () FROM empsalary;
salary | sum
--------±------
5200 | 47100
5000 | 47100
3500 | 47100
4800 | 47100
3900 | 47100
4200 | 47100
4500 | 47100
4800 | 47100
6000 | 47100
5200 | 47100
(10 rows)
如上所示，由于在OVER子句中没有ORDER BY，窗口帧和分区一样，而如果缺少PARTITION
BY则和整个表一样。换句话说，每个合计都会在整个表上进行，这样我们为每一个输出行得
到的都是相同的结果。但是如果我们加上一个ORDER BY子句，我们会得到非常不同的结果：
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
salary | sum
--------±------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 25700
4800 | 25700
5000 | 30700
5200 | 41100
5200 | 41100
6000 | 47100
(10 rows)
这里的合计是从第一个（最低的）薪水一直到当前行，包括任何与当前行相同的行（注意相
同薪水行的结果）。
窗口函数只允许出现在查询的SELECT列表和ORDER BY子句中。它们不允许出现在其他地方，
例如GROUP BY、HAVING和WHERE子句中。这是因为窗口函数的执行逻辑是在处理完这些子句
之后。另外，窗口函数在非窗口聚集函数之后执行。这意味着可以在窗口函数的参数中包括
一个聚集函数，但反过来不行。
如果需要在窗口计算执行后进行过滤或者分组，我们可以使用子查询。例如：
SELECT depname, empno, salary, enroll_date
1
还有些选项用于以其他方式定义窗口帧，但是这不包括在本教程内。详见第 4.2.8 节。
20
高级特性
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
上述查询仅仅显示了内层查询中rank低于3的结果。
当一个查询涉及到多个窗口函数时，可以将每一个分别写在一个独立的OVER子句中。但如果
多个函数要求同一个窗口行为时，这种做法是冗余的而且容易出错的。替代方案是，每一个
窗口行为可以被放在一个命名的WINDOW子句中，然后在OVER中引用它。例如：
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
关于窗口函数的更多细节可以在第 4.2.8 节、第 9.21 节、第 7.2.5 节以及SELECT参考页
中找到。
3.6. 继承
继承是面向对象数据库中的概念。它展示了数据库设计的新的可能性。
让我们创建两个表：表cities和表capitals。自然地，首都也是城市，所以我们需要有某种
方式能够在列举所有城市的时候也隐式地包含首都。如果真的聪明，我们会设计如下的模
式：
CREATE TABLE capitals (
name text,
population real,
altitude int, – (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
altitude int – (in ft)
);
CREATE VIEW cities AS
SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;
这个模式对于查询而言工作正常，但是当我们需要更新一些行时它就变得不好用了。
更好的方案是：
CREATE TABLE cities (
name text,
population real,
altitude int – (in ft)
);
21
高级特性
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
在这种情况下，一个capitals的行从它的父亲cities继承了所有列
（name、population和altitude）。列name的类型是text，一种用于变长字符串的本地
PostgreSQL类型。州首都有一个附加列state用于显示它们的州。在PostgreSQL中，一个表
可以从0个或者多个表继承。
例如，如下查询可以寻找所有海拔500尺以上的城市名称，包括州首都：
SELECT name, altitude
FROM cities
WHERE altitude > 500;
它的返回为：
name | altitude
-----------±---------
Las Vegas | 2174
Mariposa | 1953
Madison | 845
(3 rows)
在另一方面，下面的查询可以查找所有海拔高于500尺且不是州首府的城市：
SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
name | altitude
-----------±---------
Las Vegas | 2174
Mariposa | 1953
(2 rows)
其中cities之前的ONLY用于指示查询只在cities表上进行而不会涉及到继承层次中位
于cities之下的其他表。很多我们已经讨论过的命令 — SELECT、UPDATE 和DELETE — 都支
持这个ONLY记号。
注意
尽管继承很有用，但是它还未与唯一约束或外键集成，这也限制了它的可用
性。更多详情见第 5.10 节。
3.7. 小结
PostgreSQL中有很多特性在这个面向SQL新用户的教程中并未触及。有关这些特性的更多详
情将在本书的后续部分进行讨论。
如果需要更多介绍材料，请访问 PostgreSQL 官方网站2来获得更多资源链接。
2
https://www.postgresql.org
22
部分 II. SQL 语言
这部份描述在PostgreSQL中SQL语言的使用。我们从描述SQL的一般语法开始，然后解释如何创建保存数
据的结构、如何填充数据库以及如何查询它。中间的部分列出了在SQL命令中可用的数据类型和函数。
剩余的部分则留给对于调优数据性能的重要方面。
这部份的信息被组织成让一个新用户可以从头到尾跟随它来全面理解主题，而不需要多次参考后面的内
容。这些章都是自包含的，这样高级用户可以根据他们的选择阅读单独的章。这一部分的信息被以一种
叙事的风格展现。需要查看一个特定命令的完整描述的读者应该去看看第 VI 部分。
这一部分的阅读者应该知道如何连接到一个PostgreSQL数据库并且发出SQL命令。我们鼓励不熟悉这些
问题的读者先去阅读第 I 部分。SQL通常使用PostgreSQL的交互式终端psql输入，但是其他具有相似功
能的程序也可以被使用。
目录
4. SQL语法 … 31
4.1. 词法结构 … 31
4.1.1. 标识符和关键词 … 31
4.1.2. 常量 … 33
4.1.3. 操作符 … 37
4.1.4. 特殊字符 … 37
4.1.5. 注释 … 38
4.1.6. 操作符优先级 … 38
4.2. 值表达式 … 39
4.2.1. 列引用 … 40
4.2.2. 位置参数 … 40
4.2.3. 下标 … 40
4.2.4. 域选择 … 41
4.2.5. 操作符调用 … 41
4.2.6. 函数调用 … 42
4.2.7. 聚集表达式 … 42
4.2.8. 窗口函数调用 … 44
4.2.9. 类型转换 … 46
4.2.10. 排序规则表达式 … 47
4.2.11. 标量子查询 … 48
4.2.12. 数组构造器 … 48
4.2.13. 行构造器 … 49
4.2.14. 表达式计算规则 … 51
4.3. 调用函数 … 52
4.3.1. 使用位置记号 … 52
4.3.2. 使用命名记号 … 53
4.3.3. 使用混合记号 … 53
5. 数据定义 … 55
5.1. 表基础 … 55
5.2. 默认值 … 56
5.3. 生成列 … 57
5.4. 约束 … 57
5.4.1. 检查约束 … 58
5.4.2. 非空约束 … 60
5.4.3. 唯一约束 … 60
5.4.4. 主键 … 61
5.4.5. 外键 … 62
5.4.6. 排他约束 … 64
5.5. 系统列 … 65
5.6. 修改表 … 65
5.6.1. 增加列 … 66
5.6.2. 移除列 … 66
5.6.3. 增加约束 … 67
5.6.4. 移除约束 … 67
5.6.5. 更改列的默认值 … 67
5.6.6. 修改列的数据类型 … 67
5.6.7. 重命名列 … 68
5.6.8. 重命名表 … 68
5.7. 权限 … 68
5.8. 行安全性策略 … 72
5.9. 模式 … 77
5.9.1. 创建模式 … 78
5.9.2. 公共模式 … 79
5.9.3. 模式搜索路径 … 79
5.9.4. 模式和权限 … 80
24
SQL 语言
5.9.5. 系统目录模式 … 81
5.9.6. 使用模式 … 81
5.9.7. 可移植性 … 81
5.10. 继承 … 82
5.10.1. 警告 … 84
5.11. 表分区 … 85
5.11.1. 概述 … 85
5.11.2. 声明式划分 … 86
5.11.3. 使用继承实现 … 89
5.11.4. 分区剪枝 … 93
5.11.5. 分区和约束排除 … 95
5.11.6. 声明分区最佳实践 … 95
5.12. 外部数据 … 96
5.13. 其他数据库对象 … 96
5.14. 依赖跟踪 … 97
6. 数据操纵 … 99
6.1. 插入数据 … 99
6.2. 更新数据 … 100
6.3. 删除数据 … 101
6.4. 从修改的行中返回数据 … 101
7. 查询 … 103
7.1. 概述 … 103
7.2. 表表达式 … 103
7.2.1. FROM子句 … 103
7.2.2. WHERE子句 … 111
7.2.3. GROUP BY和HAVING子句 … 112
7.2.4. GROUPING SETS、CUBE和ROLLUP … 114
7.2.5. 窗口函数处理 … 117
7.3. 选择列表 … 117
7.3.1. 选择列表项 … 117
7.3.2. 列标签 … 117
7.3.3. DISTINCT … 118
7.4. 组合查询 … 118
7.5. 行排序 … 119
7.6. LIMIT和OFFSET … 120
7.7. VALUES列表 … 120
7.8. WITH查询（公共表表达式） … 121
7.8.1. WITH中的SELECT … 121
7.8.2. WITH中的数据修改语句 … 125
8. 数据类型 … 128
8.1. 数字类型 … 129
8.1.1. 整数类型 … 130
8.1.2. 任意精度数字 … 130
8.1.3. 浮点类型 … 131
8.1.4. 序数类型 … 133
8.2. 货币类型 … 134
8.3. 字符类型 … 134
8.4. 二进制数据类型 … 136
8.4.1. bytea的十六进制格式 … 136
8.4.2. bytea的转义格式 … 137
8.5. 日期/时间类型 … 138
8.5.1. 日期/时间输入 … 139
8.5.2. 日期/时间输出 … 142
8.5.3. 时区 … 143
8.5.4. 间隔输入 … 144
8.5.5. 间隔输出 … 146
8.6. 布尔类型 … 146
8.7. 枚举类型 … 147
25
SQL 语言
8.7.1. 枚举类型的声明 … 147
8.7.2. 排序 … 148
8.7.3. 类型安全性 … 148
8.7.4. 实现细节 … 149
8.8. 几何类型 … 149
8.8.1. 点 … 149
8.8.2. 线 … 150
8.8.3. 线段 … 150
8.8.4. 方框 … 150
8.8.5. 路径 … 150
8.8.6. 多边形 … 151
8.8.7. 圆 … 151
8.9. 网络地址类型 … 151
8.9.1. inet … 152
8.9.2. cidr … 152
8.9.3. inet vs. cidr … 153
8.9.4. macaddr … 153
8.9.5. macaddr8 … 153
8.10. 位串类型 … 154
8.11. 文本搜索类型 … 154
8.11.1. tsvector … 155
8.11.2. tsquery … 156
8.12. UUID类型 … 157
8.13. XML类型 … 158
8.13.1. 创建XML值 … 158
8.13.2. 编码处理 … 159
8.13.3. 访问XML值 … 159
8.14. JSON 类型 … 159
8.14.1. JSON 输入和输出语法 … 161
8.14.2. 设计 JSON 文档 … 162
8.14.3. jsonb 包含和存在 … 162
8.14.4. jsonb 索引 … 163
8.14.5. 转换 … 166
8.14.6. jsonpath Type … 166
8.15. 数组 … 167
8.15.1. 数组类型的定义 … 167
8.15.2. 数组值输入 … 168
8.15.3. 访问数组 … 169
8.15.4. 修改数组 … 171
8.15.5. 在数组中搜索 … 174
8.15.6. 数组输入和输出语法 … 175
8.16. 组合类型 … 176
8.16.1. 组合类型的声明 … 176
8.16.2. 构造组合值 … 177
8.16.3. 访问组合类型 … 178
8.16.4. 修改组合类型 … 178
8.16.5. 在查询中使用组合类型 … 179
8.16.6. 组合类型输入和输出语法 … 181
8.17. 范围类型 … 182
8.17.1. 内建范围类型 … 182
8.17.2. 例子 … 182
8.17.3. 包含和排除边界 … 183
8.17.4. 无限（无界）范围 … 183
8.17.5. 范围输入/输出 … 183
8.17.6. 构造范围 … 184
8.17.7. 离散范围类型 … 184
8.17.8. 定义新的范围类型 … 185
8.17.9. 索引 … 186
26
SQL 语言
8.17.10. 范围上的约束 … 186
8.18. 域类型 … 187
8.19. 对象标识符类型 … 187
8.20. pg_lsn 类型 … 189
8.21. 伪类型 … 189
9. 函数和操作符 … 191
9.1. 逻辑操作符 … 191
9.2. 比较函数和操作符 … 191
9.3. 数学函数和操作符 … 194
9.4. 字符串函数和操作符 … 197
9.4.1. format … 208
9.5. 二进制串函数和操作符 … 209
9.6. 位串函数和操作符 … 211
9.7. 模式匹配 … 212
9.7.1. LIKE … 213
9.7.2. SIMILAR TO正则表达式 … 213
9.7.3. POSIX正则表达式 … 215
9.8. 数据类型格式化函数 … 227
9.9. 时间/日期函数和操作符 … 234
9.9.1. EXTRACT, date_part … 239
9.9.2. date_trunc … 243
9.9.3. AT TIME ZONE … 244
9.9.4. 当前日期/时间 … 245
9.9.5. 延时执行 … 246
9.10. 枚举支持函数 … 247
9.11. 几何函数和操作符 … 247
9.12. 网络地址函数和操作符 … 251
9.13. 文本搜索函数和操作符 … 253
9.14. XML 函数 … 259
9.14.1. 产生 XML 内容 … 259
9.14.2. XML 谓词 … 263
9.14.3. 处理 XML … 264
9.14.4. 将表映射到 XML … 268
9.15. JSON 函数和操作符 … 271
9.15.1. 处理和创建JSON数据 … 272
9.15.2. SQL/JSON路径语言 … 280
9.16. 序列操作函数 … 285
9.17. 条件表达式 … 288
9.17.1. CASE … 288
9.17.2. COALESCE … 289
9.17.3. NULLIF … 290
9.17.4. GREATEST和LEAST … 290
9.18. 数组函数和操作符 … 290
9.19. 范围函数和操作符 … 293
9.20. 聚集函数 … 294
9.21. 窗口函数 … 300
9.22. 子查询表达式 … 302
9.22.1. EXISTS … 302
9.22.2. IN … 302
9.22.3. NOT IN … 303
9.22.4. ANY/SOME … 303
9.22.5. ALL … 304
9.22.6. 单一行比较 … 304
9.23. 行和数组比较 … 304
9.23.1. IN … 305
9.23.2. NOT IN … 305
9.23.3. ANY/SOME (array) … 305
9.23.4. ALL (array) … 306
27
SQL 语言
9.23.5. 行构造器比较 … 306
9.23.6. 组合类型比较 … 307
9.24. 集合返回函数 … 307
9.25. 系统信息函数和运算符 … 310
9.26. 系统管理函数 … 325
9.26.1. 配置设定函数 … 325
9.26.2. 服务器信号函数 … 326
9.26.3. 备份控制函数 … 326
9.26.4. 恢复控制函数 … 329
9.26.5. 快照同步函数 … 330
9.26.6. 复制函数 … 331
9.26.7. 数据库对象管理函数 … 334
9.26.8. 索引维护函数 … 337
9.26.9. 通用文件访问函数 … 337
9.26.10. 咨询锁函数 … 339
9.27. 触发器函数 … 341
9.28. 事件触发器函数 … 342
9.28.1. 在命令结束处捕捉更改 … 342
9.28.2. 处理被 DDL 命令删除的对象 … 342
9.28.3. 处理表重写事件 … 343
9.29. Statistics Information Functions … 344
9.29.1. 检查MCV列表 … 344
10. 类型转换 … 345
10.1. 概述 … 345
10.2. 操作符 … 346
10.3. 函数 … 349
10.4. 值存储 … 353
10.5. UNION、CASE和相关结构 … 354
10.6. SELECT的输出列 … 355
11. 索引 … 357
11.1. 简介 … 357
11.2. 索引类型 … 358
11.3. 多列索引 … 359
11.4. 索引和ORDER BY … 360
11.5. 组合多个索引 … 361
11.6. 唯一索引 … 362
11.7. 表达式索引 … 362
11.8. 部分索引 … 363
11.9. 只用索引的扫描和覆盖索引 … 365
11.10. 操作符类和操作符族 … 367
11.11. 索引和排序规则 … 368
11.12. 检查索引使用 … 369
12. 全文搜索 … 371
12.1. 介绍 … 371
12.1.1. 什么是一个文档？ … 371
12.1.2. 基本文本匹配 … 372
12.1.3. 配置 … 374
12.2. 表和索引 … 374
12.2.1. 搜索一个表 … 374
12.2.2. 创建索引 … 375
12.3. 空值文本搜索 … 376
12.3.1. 解析文档 … 376
12.3.2. 解析查询 … 377
12.3.3. 排名搜索结果 … 380
12.3.4. 加亮结果 … 381
12.4. 额外特性 … 382
12.4.1. 操纵文档 … 383
12.4.2. 操纵查询 … 383
28
SQL 语言
12.4.3. 用于自动更新的触发器 … 385
12.4.4. 收集文档统计数据 … 387
12.5. 解析器 … 387
12.6. 词典 … 389
12.6.1. 停用词 … 390
12.6.2. 简单词典 … 390
12.6.3. 同义词词典 … 392
12.6.4. 分类词典 … 393
12.6.5. Ispell 词典 … 395
12.6.6. Snowball 词典 … 397
12.7. 配置例子 … 398
12.8. 测试和调试文本搜索 … 399
12.8.1. 配置测试 … 399
12.8.2. 解析器测试 … 401
12.8.3. 词典测试 … 402
12.9. GIN 和 GiST 索引类型 … 403
12.10. psql支持 … 404
12.11. 限制 … 406
13. 并发控制 … 408
13.1. 介绍 … 408
13.2. 事务隔离 … 408
13.2.1. 读已提交隔离级别 … 409
13.2.2. 可重复读隔离级别 … 410
13.2.3. 可序列化隔离级别 … 411
13.3. 显式锁定 … 413
13.3.1. 表级锁 … 413
13.3.2. 行级锁 … 415
13.3.3. 页级锁 … 416
13.3.4. 死锁 … 416
13.3.5. 咨询锁 … 417
13.4. 应用级别的数据完整性检查 … 417
13.4.1. 用可序列化事务来强制一致性 … 418
13.4.2. 使用显式锁定强制一致性 … 418
13.5. 提醒 … 419
13.6. 锁定和索引 … 419
14. 性能提示 … 420
14.1. 使用EXPLAIN … 420
14.1.1. EXPLAIN基础 … 420
14.1.2. EXPLAIN ANALYZE … 425
14.1.3. 警告 … 429
14.2. 规划器使用的统计信息 … 430
14.2.1. 单列统计信息 … 430
14.2.2. 扩展统计信息 … 431
14.3. 用显式JOIN子句控制规划器 … 434
14.4. 填充一个数据库 … 436
14.4.1. 禁用自动提交 … 436
14.4.2. 使用COPY … 436
14.4.3. 移除索引 … 436
14.4.4. 移除外键约束 … 436
14.4.5. 增加maintenance_work_mem … 437
14.4.6. 增加max_wal_size … 437
14.4.7. 禁用 WAL 归档和流复制 … 437
14.4.8. 事后运行ANALYZE … 437
14.4.9. 关于pg_dump的一些注记 … 437
14.5. 非持久设置 … 438
15. 并行查询 … 439
15.1. 并行查询如何工作 … 439
15.2. 何时会用到并行查询？ … 440
29
SQL 语言
15.3. 并行计划 … 440
15.3.1. 并行扫描 … 440
15.3.2. 并行连接 … 441
15.3.3. 并行聚集 … 441
15.3.4. 并行Append … 441
15.3.5. 并行计划小贴士 … 442
15.4. 并行安全性 … 442
15.4.1. 为函数和聚集加并行标签 … 442
30
第 4 章 SQL语法
这一章描述了SQL的语法。它构成了理解后续具体介绍如何使用SQL定义和修改数据的章节的
基础。
我们同时建议已经熟悉SQL的用户仔细阅读本章，因为本章包含一些在SQL数据库中实现得不
一致的以及PostgreSQL中特有的规则和概念。
4.1. 词法结构
SQL输入由一个命令序列组成。一个命令由一个记号的序列构成，并由一个分号（“;”）终
结。输入流的末端也会标志一个命令的结束。具体哪些记号是合法的与具体命令的语法有
关。
一个记号可以是一个关键词、一个标识符、一个带引号的标识符、一个literal（或常量）
或者一个特殊字符符号。记号通常以空白（空格、制表符、新行）来分隔，但在无歧义时并
不强制要求如此（唯一的例子是一个特殊字符紧挨着其他记号）。
例如，下面是一个（语法上）合法的SQL输入：
SELECT * FROM MY_TABLE;
UPDATE MY_TABLE SET A = 5;
INSERT INTO MY_TABLE VALUES (3, ‘hi there’);
这是一个由三个命令组成的序列，每一行一个命令（尽管这不是必须地，在同一行中可以有
超过一个命令，而且命令还可以被跨行分割）。
另外，注释也可以出现在SQL输入中。它们不是记号，它们和空白完全一样。
根据标识命令、操作符、参数的记号不同，SQL的语法不很一致。最前面的一些记号通常是
命令名，因此在上面的例子中我们通常会说一个“SELECT”、一个“UPDATE”和一
个“INSERT”命令。但是例如UPDATE命令总是要求一个SET记号出现在一个特定位置，
而INSERT则要求一个VALUES来完成命令。每个命令的精确语法规则在第 VI 部分中介绍。
4.1.1. 标识符和关键词
上例中的SELECT、UPDATE或VALUES记号是关键词的例子，即SQL语言中具有特定意义的词。
记号MY_TABLE和A则是标识符的例子。它们标识表、列或者其他数据库对象的名字，取决于
使用它们的命令。因此它们有时也被简称为“名字”。关键词和标识符具有相同的词法结
构，这意味着我们无法在没有语言知识的前提下区分一个标识符和关键词。一个关键词的完
整列表可以在附录 C中找到。
SQL标识符和关键词必须以一个字母（a-z，也可以是带变音符的字母和非拉丁字母）或一个
下划线（）开始。后续字符可以是字母、下划线（）、数字（0-9）或美元符号（ $KaTeX parse error: Expected 'EOF', got '&' at position 778: \dotsnicode字符。这种变体以U&̲（大写或小写U跟上一个花号）\dots$ ）、一个可选的另个或更多字符的“标签”、另一个美元
符号、一个构成字符串内容的任意字符序列、一个美元符号、开始这个美元引用的相同标签
和一个美元符号组成。例如，这里有两种不同的方法使用美元引用指定字符串“Dianne’s
horse”：
$D iann e^{'} s h orse$
$S o m e T a g$ Dianne’s horse $S o m e T a g$
注意在美元引用字符串中，单引号可以在不被转义的情况下使用。事实上，在一个美元引用
字符串中不需要对字符进行转义：字符串内容总是按其字面意思写出。反斜线不是特殊的，
并且美元符号也不是特殊的，除非它们是匹配开标签的一个序列的一部分。
可以通过在每一个嵌套级别上选择不同的标签来嵌套美元引用字符串常量。这最常被用在编
写函数定义上。例如：
$f u n c t i o n$
BEGIN
RETURN ($1 ~ $q$ [\t\r\n\v\] $q$ );
END;
$f u n c t i o n$
这里，序列 $q$ [\t\r\n\v\] $q$ 表示一个美元引用的文字串[\t\r\n\v\]，当该函数体被
PostgreSQL执行时它将被识别。但是因为该序列不匹配外层的美元引用的定界符$function
$，它只是一些在外层字符串所关注的常量中的字符而已。一个美元引用字符串的标签（如果有）遵循一个未被引用标识符的相同规则，除了它不能包含一个美元符号之外。标签是大小写敏感的，因此$ tag $St r in g co n t e n t$ tag $是正确的，但是$ TAG $St r in g co n t e n t$ tag$不正确。
一个跟着一个关键词或标识符的美元引用字符串必须用空白与之分隔开，否则美元引用定界
符可能会被作为前面标识符的一部分。
美元引用不是 SQL 标准的一部分，但是在书写复杂字符串文字方面，它常常是一种比兼容标
准的单引号语法更方便的方法。当要表示的字符串常量位于其他常量中时它特别有用，这种
情况常常在过程函数定义中出现。如果用单引号语法，上一个例子中的每个反斜线将必须被
写成四个反斜线，这在解析原始字符串常量时会被缩减到两个反斜线，并且接着在函数执行
期间重新解析内层字符串常量时变成一个。
4.1.2.5. 位串常量
位串常量看起来像常规字符串常量在开引号之前（中间无空白）加了一个B（大写或小写形
式），例如B’1001’。位串常量中允许的字符只有0和1。
作为一种选择，位串常量可以用十六进制记号法指定，使用一个前导X（大写或小写形式）,
例如X’1FF’。这种记号法等价于一个用四个二进制位取代每个十六进制位的位串常量。
两种形式的位串常量可以以常规字符串常量相同的方式跨行继续。美元引用不能被用在位串
常量中。
35
SQL语法
4.1.2.6. 数字常量
在这些一般形式中可以接受数字常量：
digits
digits.[digits][e[±]digits]
[digits].digits[e[±]digits]
digitse[±]digits
其中digits是一个或多个十进制数字（0 到 9）。如果使用了小数点，在小数点前面或后面
必须至少有一个数字。如果存在一个指数标记（e），在其后必须跟着至少一个数字。在该
常量中不能嵌入任何空白或其他字符。注意任何前导的加号或减号并不实际被考虑为常量的
一部分，它是一个应用到该常量的操作符。
这些是合法数字常量的例子：
42
3.5
4.
.001
5e2
1.925e-3
如果一个不包含小数点和指数的数字常量的值适合类型integer（32 位），它首先被假定
为类型integer。否则如果它的值适合类型bigint（64 位），它被假定为类型bigint。再否
则它会被取做类型numeric。包含小数点和/或指数的常量总是首先被假定为类型numeric。
一个数字常量初始指派的数据类型只是类型转换算法的一个开始点。在大部分情况中，常量
将被根据上下文自动被强制到最合适的类型。必要时，你可以通过造型它来强制一个数字值
被解释为一种指定数据类型。例如，你可以这样强制一个数字值被当做类
型real（float4）：
REAL ‘1.23’ – string style
1.23::REAL – PostgreSQL (historical) style
这些实际上只是接下来要讨论的一般造型记号的特例。
4.1.2.7. 其他类型的常量
一种任意类型的一个常量可以使用下列记号中的任意一种输入：
type ‘string’
‘string’::type
CAST ( ‘string’ AS type )
字符串常量的文本被传递到名为type的类型的输入转换例程中。其结果是指定类型的一个常
量。如果对该常量的类型没有歧义（例如，当它被直接指派给一个表列时），显式类型造型
可以被忽略，在那种情况下它会被自动强制。
字符串常量可以使用常规 SQL 记号或美元引用书写。
也可以使用一个类似函数的语法来指定一个类型强制：
typename ( ‘string’ )
36
SQL语法
但是并非所有类型名都可以用在这种方法中，详见第 4.2.9 节。
如第 4.2.9 节中讨论的，::、CAST()以及函数调用语法也可以被用来指定任意表达式的
运行时类型转换。要避免语法歧义，type 'string’语法只能被用来指定简单文字常量的类
型。type 'string’语法上的另一个限制是它无法对数组类型工作，指定一个数组常量的类
型可使用::或CAST()。
CAST()语法符合 SQL。type 'string’语法是该标准的一般化：SQL 指定这种语法只用于一些
数据类型，但是PostgreSQL允许它用于所有类型。带有::的语法是PostgreSQL的历史用法，
就像函数调用语法一样。
4.1.3. 操作符
一个操作符名是最多NAMEDATALEN-1（默认为 63）的一个字符序列，其中的字符来自下面的
列表：

- - / < > = ~ ! @ # % ^ & | ? 不过，在操作符名上有一些限制： • -- and /*不能在一个操作符名的任何地方出现，因为它们将被作为一段注释的开始。 • 一个多字符操作符名不能以+或-结尾，除非该名称也至少包含这些字符中的一个： ~ ! @ # % ^ & | ?
    例如，@-是一个被允许的操作符名，但*-不是。这些限制允许PostgreSQL解析 SQL 兼容的
    查询而不需要在记号之间有空格。
    当使用非 SQL 标准的操作符名时，你通常需要用空格分隔相邻的操作符来避免歧义。例如，
    如果你定义了一个名为@的左一元操作符，你不能写X*@Y，你必须写X* @Y来确保PostgreSQL
    把它读作两个操作符名而不是一个。
    4.1.4. 特殊字符
    一些不是数字字母的字符有一种不同于作为操作符的特殊含义。这些字符的详细用法可以在
    描述相应语法元素的地方找到。这一节只是为了告知它们的存在以及总结这些字符的目的。
    • 跟随在一个美元符号（$）后面的数字被用来表示在一个函数定义或一个预备语句中的位
    置参数。在其他上下文中该美元符号可以作为一个标识符或者一个美元引用字符串常量的
    一部分。
    • 圆括号（()）具有它们通常的含义，用来分组表达式并且强制优先。在某些情况中，圆括
    号被要求作为一个特定 SQL 命令的固定语法的一部分。
    • 方括号（[]）被用来选择一个数组中的元素。更多关于数组的信息见第 8.15 节。
    • 逗号（,）被用在某些语法结构中来分割一个列表的元素。
    • 分号（;）结束一个 SQL 命令。它不能出现在一个命令中间的任何位置，除了在一个字符
    串常量中或者一个被引用的标识符中。
    • 冒号（:）被用来从数组中选择“切片”（见第 8.15 节）。在某些 SQL 的“方言”（例
    如嵌入式 SQL）中，冒号被用来作为变量名的前缀。
    • 星号（）被用在某些上下文中标记一个表的所有域或者组合值。当它被用作一个聚集函
    数的参数时，它还有一种特殊的含义，即该聚集不要求任何显式参数。
    • 句点（.）被用在数字常量中，并且被用来分割模式、表和列名。
    37
    SQL语法
    4.1.5. 注释
    一段注释是以双斜线开始并且延伸到行结尾的一个字符序列，例如：
    – This is a standard SQL comment
    另外，也可以使用 C 风格注释块：
    / multiline comment

with nesting: /* nested block comment */
*/
这里该注释开始于/并且延伸到匹配出现的/。这些注释块可按照 SQL 标准中指定的方式嵌
套，但和 C 中不同。这样我们可以注释掉一大段可能包含注释块的代码。
在进一步的语法分析前，注释会被从输入流中被移除并且实际被替换为空白。
4.1.6. 操作符优先级
表 4.2显示了PostgreSQL中操作符的优先级和结合性。大部分操作符具有相同的优先并且是
左结合的。操作符的优先级和结合性被硬写在解析器中。
此外，当使用二元和一元操作符的组合时，有时你将需要增加圆括号。例如：
SELECT 5 ! - 6;
将被解析为：
SELECT 5 ! (- 6);
因为解析器不知道 — 知道时就为时已晚 — !被定义为一个后缀操作符而不是一个中缀操作
符。在这种情况下要得到想要的行为，你必须写成：
SELECT (5 !) - 6;
只是为了扩展性必须付出的代价。
表 4.2. 操作符优先级（从高到低）
操作符/元素结合性描述
. 左表/列名分隔符
:: 左 PostgreSQL-风格的类型转换
[ ] 左数组元素选择

- 右一元加、一元减
  ^ 左指数

/ % 左乘、除、模

- 左加、减
  （任意其他操作符）左所有其他本地以及用户定义
  的操作符
  BETWEEN IN LIKE ILIKE
  SIMILAR
  范围包含、集合成员关系、
  字符串匹配
  38
  SQL语法
  操作符/元素结合性描述
  < > = <= >= <> 比较操作符
  IS ISNULL NOTNULL IS TRUE、IS FALSE、IS
  NULL、IS DISTINCT FROM等
  NOT 右逻辑否定
  AND 左逻辑合取
  OR 左逻辑析取
  注意该操作符有限规则也适用于与上述内建操作符具有相同名称的用户定义的操作符。例
  如，如果你为某种自定义数据类型定义了一个“+”操作符，它将具有和内建的“+”操作符
  相同的优先级，不管你的操作符要做什么。
  当一个模式限定的操作符名被用在OPERATOR语法中时，如下面的例子：
  SELECT 3 OPERATOR(pg_catalog.+) 4;
  OPERATOR结构被用来为“任意其他操作符”获得表 4.2中默认的优先级。不管出现
  在OPERATOR()中的是哪个指定操作符，这都是真的。
  注意
  版本 9.5 之前的PostgreSQL使用的操作符优先级规则略有不同。特别
  是，<=、>= 和<>习惯于被当作普通操作符，IS 测试习惯于具有较高的优先
  级。并且在一些认为NOT比 BETWEEN优先级高的情况下，NOT BETWEEN 和相关的
  结构的行为不一致。为了更好地兼容 SQL 标准并且减少对逻辑上等价的结构
  不一致的处理，这些规则也得到了修改。在大部分情况下，这些变化不会导
  致行为上的变化，或者可能会产生“no such operator” 错误，但可以通过增
  加圆括号解决。不过在一些极端情况中，查询可能在没有被报告解析错误的
  情况下发生行为的改变。如果你发觉这些改变悄悄地破坏了一些事情，可以
  打开operator_precedence_warning 配置参数，然后测试你的应用看看有没有
  一些警告被记录。
  4.2. 值表达式
  值表达式被用于各种各样的环境中，例如在SELECT命令的目标列表中、作
  为INSERT或UPDATE中的新列值或者若干命令中的搜索条件。为了区别于一个表表达式（是一
  个表）的结果，一个值表达式的结果有时候被称为一个标量。值表达式因此也被称为标量表
  达式（或者甚至简称为表达式）。表达式语法允许使用算数、逻辑、集合和其他操作从原始
  部分计算值。
  一个值表达式是下列之一：
  • 一个常量或文字值
  • 一个列引用
  • 在一个函数定义体或预备语句中的一个位置参数引用
  • 一个下标表达式
  • 一个域选择表达式
  • 一个操作符调用
  • 一个函数调用
  39
  SQL语法
  • 一个聚集表达式
  • 一个窗口函数调用
  • 一个类型转换
  • 一个排序规则表达式
  • 一个标量子查询
  • 一个数组构造器
  • 一个行构造器
  • 另一个在圆括号（用来分组子表达式以及重载优先级）中的值表达式
  在这个列表之外，还有一些结构可以被分类为一个表达式，但是它们不遵循任何一般语法规
  则。这些通常具有一个函数或操作符的语义并且在第 9 章中的合适位置解释。一个例子是IS
  NULL子句。
  我们已经在第 4.1.2 节中讨论过常量。下面的小节会讨论剩下的选项。
  4.2.1. 列引用
  一个列可以以下面的形式被引用：
  correlation.columnname
  correlation是一个表（有可能以一个模式名限定）的名字，或者是在FROM子句中为一个表
  定义的别名。如果列名在当前索引所使用的表中都是唯一的，关联名称和分隔用的句点可以
  被忽略（另见第 7 章）。
  4.2.2. 位置参数
  一个位置参数引用被用来指示一个由 SQL 语句外部提供的值。参数被用于 SQL 函数定义和
  预备查询中。某些客户端库还支持独立于 SQL 命令字符串来指定数据值，在这种情况中参数
  被用来引用那些线外数据值。一个参数引用的形式是：
  $number
  例如，考虑一个函数dept的定义：
  CREATE FUNCTION dept(text) RETURNS dept
  AS $KaTeX parse error: Can't use function '$' in math mode at position 34: \dotst WHERE name = $̲1$
  LANGUAGE SQL;
  这里$1引用函数被调用时第一个函数参数的值。
  4.2.3. 下标
  如果一个表达式得到了一个数组类型的值，那么可以抽取出该数组值的一个特定元素：
  expression[subscript]
  或者抽取出多个相邻元素（一个“数组切片”）：
  40
  SQL语法
  expression[lower_subscript:upper_subscript]
  （这里，方括号[ ]表示其字面意思）。每一个下标自身是一个表达式，它必须得到一个整
  数值。
  通常，数组表达式必须被加上括号，但是当要被加下标的表达式只是一个列引用或位置参数
  时，括号可以被忽略。还有，当原始数组是多维时，多个下标可以被连接起来。例如：
  mytable.arraycolumn[4]
  mytable.two_d_column[17][34]
  $1[10:42]
  (arrayfunction(a,b))[42]
  最后一个例子中的圆括号是必需的。详见第 8.15 节。
  4.2.4. 域选择
  如果一个表达式得到一个组合类型（行类型）的值，那么可以抽取该行的指定域
  expression.fieldname
  通常行表达式必须被加上括号，但是当该表达式是仅从一个表引用或位置参数选择时，圆括
  号可以被忽略。例如：
  mytable.mycolumn
  $1.somecolumn
  (rowfunction(a,b)).col3
  （因此，一个被限定的列引用实际上只是域选择语法的一种特例）。一种重要的特例是从一
  个组合类型的表列中抽取一个域：
  (compositecol).somefield
  (mytable.compositecol).somefield
  这里需要圆括号来显示compositecol是一个列名而不是一个表名，在第二种情况中则是显
  示mytable是一个表名而不是一个模式名。
  你可以通过书写.来请求一个组合值的所有域：
  (compositecol).
  这种记法的行为根据上下文会有不同，详见第 8.16.5 节。
  4.2.5. 操作符调用
  对于一次操作符调用，有三种可能的语法：
  expression operator expression（二元中缀操作符）
  operator expression（一元前缀操作符）
  expression operator（一元后缀操作符）
  其中operator记号遵循第 4.1.3 节的语法规则，或者是关键词AND、OR和NOT之一，或者是一
  个如下形式的受限定操作符名：
  41
  SQL语法
  OPERATOR(schema.operatorname)
  哪个特定操作符存在以及它们是一元的还是二元的取决于由系统或用户定义的那些操作
  符。第 9 章描述了内建操作符。
  4.2.6. 函数调用
  一个函数调用的语法是一个函数的名称（可能受限于一个模式名）后面跟上封闭于圆括号中
  的参数列表：
  function_name ([expression [, expression … ]] )
  例如，下面会计算 2 的平方根：
  sqrt(2)
  当在一个某些用户不信任其他用户的数据库中发出查询时，在编写函数调用时应遵
  守第 10.3 节中的安全防范措施。
  内建函数的列表在第 9 章中。其他函数可以由用户增加。
  参数可以有选择地被附加名称。详见第 4.3 节。
  注意
  一个采用单一组合类型参数的函数可以被有选择地称为域选择语法，并且反过
  来域选择可以被写成函数的风格。也就是说，记号col(table)和table.col是
  可以互换的。这种行为是非 SQL 标准的但是在PostgreSQL中被提供，因为它
  允许函数的使用来模拟“计算域”。详见第 8.16.5 节。
  4.2.7. 聚集表达式
  一个聚集表达式表示在由一个查询选择的行上应用一个聚集函数。一个聚集函数将多个输入
  减少到一个单一输出值，例如对输入的求和或平均。一个聚集表达式的语法是下列之一：
  aggregate_name (expression [ , … ] [ order_by_clause ] ) [ FILTER
  ( WHERE filter_clause ) ]
  aggregate_name (ALL expression [ , … ] [ order_by_clause ] ) [ FILTER
  ( WHERE filter_clause ) ]
  aggregate_name (DISTINCT expression [ , … ] [ order_by_clause ] ) [ FILTER
  ( WHERE filter_clause ) ]
  aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]
  aggregate_name ( [ expression [ , … ] ] ) WITHIN GROUP ( order_by_clause )
  [ FILTER ( WHERE filter_clause ) ]
  这里aggregate_name是一个之前定义的聚集（可能带有一个模式名限定），并
  且expression是任意自身不包含聚集表达式的值表达式或一个窗口函数调用。可选
  的order_by_clause和filter_clause描述如下。
  第一种形式的聚集表达式为每一个输入行调用一次聚集。第二种形式和第一种相同，因
  为ALL是默认选项。第三种形式为输入行中表达式的每一个可区分值（或者对于多个表达式
  是值的可区分集合）调用一次聚集。第四种形式为每一个输入行调用一次聚集，因为没有特
  定的输入值被指定，它通常只对于count()聚集函数有用。最后一种形式被用于有序集聚集
  函数，其描述如下。
  42
  SQL语法
  大部分聚集函数忽略空输入，这样其中一个或多个表达式得到空值的行将被丢弃。除非另有
  说明，对于所有内建聚集都是这样。
  例如，count()得到输入行的总数。count(f1)得到输入行中f1为非空的数量，因为count忽
  略空值。而count(distinct f1)得到f1的非空可区分值的数量。
  一般地，交给聚集函数的输入行是未排序的。在很多情况中这没有关系，例如不管接收到什
  么样的输入，min总是产生相同的结果。但是，某些聚集函数（例如array_agg
  和string_agg）依据输入行的排序产生结果。当使用这类聚集时，可选
  的order_by_clause可以被用来指定想要的顺序。order_by_clause与查询级别的ORDER BY子
  句（如第 7.5 节所述）具有相同的语法，除非它的表达式总是仅有表达式并且不能是输出列
  名称或编号。例如：
  SELECT array_agg(a ORDER BY b DESC) FROM table;
  在处理多参数聚集函数时，注意ORDER BY出现在所有聚集参数之后。例如，要这样写：
  SELECT string_agg(a, ‘,’ ORDER BY a) FROM table;
  而不能这样写：
  SELECT string_agg(a ORDER BY a, ‘,’) FROM table; – 不正确
  后者在语法上是合法的，但是它表示用两个ORDER BY键来调用一个单一参数聚集函数（第二
  个是无用的，因为它是一个常量）。
  如果在order_by_clause之外指定了DISTINCT，那么所有的ORDER BY表达式必须匹配聚集的
  常规参数。也就是说，你不能在DISTINCT列表没有包括的表达式上排序。
  注意
  在一个聚集函数中指定DISTINCT以及ORDER BY的能力是一种PostgreSQL扩展。
  按照到目前为止的描述，如果一般目的和统计性聚集中排序是可选的，在要为它排序输入
  行时可以在该聚集的常规参数列表中放置ORDER BY。有一个聚集函数的子集叫做有序集聚
  集，它要求一个 order_by_clause，通常是因为该聚集的计算只对其输入行的特定顺序有意
  义。有序集聚集的典型例子包括排名和百分位计算。按照上文的最后一种语法，对于一个
  有序集聚集， order_by_clause被写在 WITHIN GROUP (…)中。 order_by_clause中的表达
  式会像普通聚集参数一样对每一个输入行计算一次，按照每个 order_by_clause的要求排序
  并且交给该聚集函数作为输入参数（这和非 WITHIN GROUP order_by_clause的情况不同，
  在其中表达式的结果不会被作为聚集函数的参数）。如果有在 WITHIN GROUP之前的参数表
  达式，会把它们称为直接参数以便与列在 order_by_clause中的聚集参数相区分。与普通
  聚集参数不同，针对每次聚集调用只会计算一次直接参数，而不是为每一个输入行计算一
  次。这意味着只有那些变量被GROUP BY 分组时，它们才能包含这些变量。这个限制同样适用
  于根本不在一个聚集表达式内部的直接参数。直接参数通常被用于百分数之类的东西，它
  们只有作为每次聚集计算用一次的单一值才有意义。直接参数列表可以为空，在这种情况
  下，写成() 而不是(*)（实际上 PostgreSQL接受两种拼写，但是只有第一种符合 SQL 标
  准）。
  有序集聚集的调用例子：
  SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;
  percentile_cont

43
SQL语法
50489
这会从表households的 income列得到第 50 个百分位或者中位的值。这里0.5是一个直接参
数，对于百分位部分是一个在不同行之间变化的值的情况它没有意义。
如果指定了FILTER，那么只有对filter_clause计算为真的输入行会被交给该聚集函数，其
他行会被丢弃。例如：
SELECT
count() AS unfiltered,
count() FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
unfiltered | filtered
------------±---------
10 | 4
(1 row)
预定义的聚集函数在第 9.20 节中描述。其他聚集函数可以由用户增加。
一个聚集表达式只能出现在SELECT命令的结果列表或是HAVING子句中。在其他子句
（如WHERE）中禁止使用它，因为那些子句的计算在逻辑上是在聚集的结果被形成之前。
当一个聚集表达式出现在一个子查询中（见第 4.2.11 节和第 9.22 节），聚集通常在该子
查询的行上被计算。但是如果该聚集的参数（以及filter_clause，如果有）只包含外层变
量则会产生一个异常：该聚集则属于最近的那个外层，并且会在那个查询的行上被计算。该
聚集表达式从整体上则是对其所出现于的子查询的一种外层引用，并且在那个子查询的任意
一次计算中都作为一个常量。只出现在结果列表或HAVING子句的限制适用于该聚集所属的查
询层次。
4.2.8. 窗口函数调用
一个窗口函数调用表示在一个查询选择的行的某个部分上应用一个聚集类的函数。和非窗口
聚集函数调用不同，这不会被约束为将被选择的行分组为一个单一的输出行 — 在查询输出
中每一个行仍保持独立。不过，窗口函数能够根据窗口函数调用的分组声明（PARTITION
BY列表）访问属于当前行所在分组中的所有行。一个窗口函数调用的语法是下列之一：
function_name ([expression [, expression … ]]) [ FILTER ( WHERE filter_clause
) ] OVER window_name
function_name ([expression [, expression … ]]) [ FILTER ( WHERE filter_clause
) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition
)
其中window_definition的语法是
[ existing_window_name ]
[ PARTITION BY expression [, …] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ]
[, …] ]
[ frame_clause ]
可选的frame_clause是下列之一
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
44
SQL语法
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
其中frame_start和frame_end可以是下面形式中的一种
UNBOUNDED PRECEDING
offset PRECEDING
CURRENT ROW
offset FOLLOWING
UNBOUNDED FOLLOWING
而frame_exclusion可以是下列之一
EXCLUDE CURRENT ROW
EXCLUDE GROUP
EXCLUDE TIES
EXCLUDE NO OTHERS
这里，expression表示任何自身不含有窗口函数调用的值表达式。
window_name是对定义在查询的WINDOW子句中的一个命名窗口声明的引用。还可以使用
在WINDOW子句中定义命名窗口的相同语法在圆括号内给定一个完整的window_definition，
详见SELECT参考页。值得指出的是，OVER wname并不严格地等价于OVER (wname …)，后者
表示复制并修改窗口定义，并且在被引用窗口声明包括一个帧子句时会被拒绝。
PARTITION BY选项将查询的行分组成为分区，窗口函数会独立地处理它们。PARTITION BY工
作起来类似于一个查询级别的GROUP BY子句，不过它的表达式总是只是表达式并且不能是输
出列的名称或编号。如果没有PARTITION BY，该查询产生的所有行被当作一个单一分区来处
理。ORDER BY选项决定被窗口函数处理的一个分区中的行的顺序。它工作起来类似于一个查
询级别的ORDER BY子句，但是同样不能使用输出列的名称或编号。如果没有ORDER BY，行将
被以未指定的顺序被处理。
frame_clause指定构成窗口帧的行集合，它是当前分区的一个子集，窗口函数将作用在该帧
而不是整个分区。帧中的行集合会随着哪一行是当前行而变化。在RANGE、ROWS或
者GROUPS模式中可以指定帧，在每一种情况下，帧的范围都是从frame_start到frame_end。
如果frame_end被省略，则末尾默认为CURRENT ROW。
UNBOUNDED PRECEDING的一个frame_start表示该帧开始于分区的第一行，类似地UNBOUNDED
FOLLOWING的一个frame_end表示该帧结束于分区的最后一行。
在RANGE或GROUPS模式中，CURRENT ROW的一个frame_start表示帧开始于当前行的第一
个平级行（被窗口的ORDER BY子句排序为与当前行等效的行），而CURRENT ROW的一
个frame_end表示帧结束于当前行的最后一个平级行。在ROWS模式中，CURRENT ROW就表示当
前行。
在offset PRECEDING以及offset FOLLOWING帧选项中，offset必须是一个不包含任何变量、
聚集函数或者窗口函数的表达式。offset的含义取决于帧模式：
• 在ROWS模式中，offset必须得到一个非空、非负的整数，并且该选项表示帧开始于当前行
之前或者之后指定数量的行。
• 在GROUPS模式中，offset也必须得到一个非空、非负的整数，并且该选项表示帧开始于当
前行的平级组之前或者之后指定数量的平级组，这里平级组是在ORDER BY顺序中等效的行
集合（要使用GROUPS模式，在窗口定义中就必须有一个ORDER BY子句）。
• 在RANGE模式中，这些选项要求ORDER BY子句正好指定一列。offset指定当前行中那一列
的值与它在该帧中前面或后面的行中的列值的最大差值。offset表达式的数据类型会随着
排序列的数据类型而变化。对于数字的排序列，它通常是与排序列相同的类型，但对于日
期时间排序列它是一个interval。例如，如果排序列是类型date或者timestamp，我们可
45
SQL语法
以写RANGE BETWEEN ‘1 day’ PRECEDING AND ‘10 days’ FOLLOWING。offset仍然要求是非
空且非负，不过“非负”的含义取决于它的数据类型。
在任何一种情况下，到帧末尾的距离都受限于到分区末尾的距离，因此对于离分区末尾比较
近的行来说，帧可能会包含比较少的行。
注意在ROWS以及GROUPS模式中，0 PRECEDING和0 FOLLOWING与CURRENT ROW等效。通常
在RANGE模式中，这个结论也成立（只要有一种合适的、与数据类型相关的“零”的含
义）。
frame_exclusion选项允许当前行周围的行被排除在帧之外，即便根据帧的开始和结束选项
应该把它们包括在帧中。EXCLUDE CURRENT ROW会把当前行排除在帧之外。EXCLUDE GROUP会
把当前行以及它在顺序上的平级行都排除在帧之外。EXCLUDE TIES把当前行的任何平级行都
从帧中排除，但不排除当前行本身。EXCLUDE NO OTHERS只是明确地指定不排除当前行或其平
级行的这种默认行为。
默认的帧选项是RANGE UNBOUNDED PRECEDING，它和RANGE BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW相同。如果使用ORDER BY，这会把该帧设置为从分区开始一直到当前行的最后一
个ORDER BY平级行的所有行。如果不使用ORDER BY，就意味着分区中所有的行都被包括在窗
口帧中，因为所有行都成为了当前行的平级行。
限制是frame_start不能是UNBOUNDED FOLLOWING、frame_end不能是UNBOUNDED PRECEDING，
并且在上述frame_start和frame_end选项的列表中frame_end选择不能早于frame_start选择
出现 — 例如不允许RANGE BETWEEN CURRENT ROW AND offset PRECEDING，但允许ROWS
BETWEEN 7 PRECEDING AND 8 PRECEDING，虽然它不会选择任何行。
如果指定了FILTER，那么只有对filter_clause计算为真的输入行会被交给该窗口函数，其
他行会被丢弃。只有是聚集的窗口函数才接受FILTER 。
内建的窗口函数在表 9.60中介绍。用户可以加入其他窗口函数。此外，任何内建的或者用
户定义的通用聚集或者统计性聚集都可以被用作窗口函数（有序集和假想集聚集当前不能被
用作窗口函数）。
使用的语法被用来把参数较少的聚集函数当作窗口函数调用，例如count() OVER
(PARTITION BY x ORDER BY y)。星号（）通常不被用于窗口相关的函数。窗口相关的函数
不允许在函数参数列表中使用DISTINCT或ORDER BY。
只有在SELECT列表和查询的ORDER BY子句中才允许窗口函数调用。
更多关于窗口函数的信息可以在第 3.5 节、第 9.21 节以及第 7.2.5 节中找到。
4.2.9. 类型转换
一个类型造型指定从一种数据类型到另一种数据类型的转换。PostgreSQL接受两种等价的类
型造型语法：
CAST ( expression AS type )
expression::type
CAST语法遵从 SQL，而用::的语法是PostgreSQL的历史用法。
当一个造型被应用到一种未知类型的值表达式上时，它表示一种运行时类型转换。只有已经
定义了一种合适的类型转换操作时，该造型才会成功。注意这和常量的造型
（如第 4.1.2.7 节中所示）使用不同。应用于一个未修饰串文字的造型表示一种类型到一个
文字常量值的初始赋值，并且因此它将对任意类型都成功（如果该串文字的内容对于该数据
类型的输入语法是可接受的）。
如果一个值表达式必须产生的类型没有歧义（例如当它被指派给一个表列），通常可以省略
显式类型造型，在这种情况下系统会自动应用一个类型造型。但是，只有对在系统目录中被
46
SQL语法
标记为“OK to apply implicitly”的造型才会执行自动造型。其他造型必须使用显式造型
语法调用。这种限制是为了防止出人意料的转换被无声无息地应用。
还可以用像函数的语法来指定一次类型造型：
typename ( expression )
不过，这只对那些名字也作为函数名可用的类型有效。例如，double precision不能以这种
方式使用，但是等效的float8可以。还有，如果名称interval、time和timestamp被用双引
号引用，那么由于语法冲突的原因，它们只能以这种风格使用。因此，函数风格的造型语法
的使用会导致不一致性并且应该尽可能被避免。
注意
函数风格的语法事实上只是一次函数调用。当两种标准造型语法之一被用来做
一次运行时转换时，它将在内部调用一个已注册的函数来执行该转换。简而言
之，这些转换函数具有和它们的输出类型相同的名字，并且因此“函数风格的
语法”无非是对底层转换函数的一次直接调用。显然，一个可移植的应用不应
当依赖于它。详见CREATE CAST。
4.2.10. 排序规则表达式
COLLATE子句会重载一个表达式的排序规则。它被追加到它适用的表达式：
expr COLLATE collation
这里collation可能是一个受模式限定的标识符。COLLATE子句比操作符绑得更紧，需要时可
以使用圆括号。
如果没有显式指定排序规则，数据库系统会从表达式所涉及的列中得到一个排序规则，如果
该表达式没有涉及列，则会默认采用数据库的默认排序规则。
COLLATE子句的两种常见使用是重载ORDER BY子句中的排序顺序，例如：
SELECT a, b, c FROM tbl WHERE … ORDER BY a COLLATE “C”;
以及重载具有区域敏感结果的函数或操作符调用的排序规则，例如：
SELECT FROM tbl WHERE a > ‘foo’ COLLATE “C”;
注意在后一种情况中，COLLATE子句被附加到我们希望影响的操作符的一个输入参数
上。COLLATE子句被附加到该操作符或函数调用的哪个参数上无关紧要，因为被操作符或函
数应用的排序规则是考虑所有参数得来的，并且一个显式的COLLATE子句将重载所有其他参
数的排序规则（不过，附加非匹配COLLATE子句到多于一个参数是一种错误。详
见第 23.2 节）。因此，这会给出和前一个例子相同的结果：
SELECT * FROM tbl WHERE a COLLATE “C” > ‘foo’;
但是这是一个错误：
SELECT * FROM tbl WHERE (a > ‘foo’) COLLATE “C”;
47
SQL语法
因为它尝试把一个排序规则应用到>操作符的结果，而它的数据类型是非可排序数据类
型boolean。
4.2.11. 标量子查询
一个标量子查询是一种圆括号内的普通SELECT查询，它刚好返回一行一列（关于书写查询可
见第 7 章）。SELECT查询被执行并且该单一返回值被使用在周围的值表达式中。将一个返回
超过一行或一列的查询作为一个标量子查询使用是一种错误（但是如果在一次特定执行期间
该子查询没有返回行则不是错误，该标量结果被当做为空）。该子查询可以从周围的查询中
引用变量，这些变量在该子查询的任何一次计算中都将作为常量。对于其他涉及子查询的表
达式还可见第 9.22 节。
例如，下列语句会寻找每个州中最大的城市人口：
SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
FROM states;
4.2.12. 数组构造器
一个数组构造器是一个能构建一个数组值并且将值用于它的成员元素的表达式。一个简单的
数组构造器由关键词ARRAY、一个左方括号[、一个用于数组元素值的表达式列表（用逗号分
隔）以及最后的一个右方括号]组成。例如：
SELECT ARRAY[1,2,3+4];
array

{1,2,7}
(1 row)
默认情况下，数组元素类型是成员表达式的公共类型，使用和UNION或CASE结构
（见第 10.5 节）相同的规则决定。你可以通过显式将数组构造器造型为想要的类型来重
载，例如：
SELECT ARRAY[1,2,22.7]::integer[];
array

{1,2,23}
(1 row)
这和把每一个表达式单独地造型为数组元素类型的效果相同。关于造型的更多信息请
见第 4.2.9 节。
多维数组值可以通过嵌套数组构造器来构建。在内层的构造器中，关键词ARRAY可以被忽
略。例如，这些语句产生相同的结果：
SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];
array

{{1,2},{3,4}}
(1 row)
SELECT ARRAY[[1,2],[3,4]];
array

{{1,2},{3,4}}
48
SQL语法
(1 row)
因为多维数组必须是矩形的，处于同一层次的内层构造器必须产生相同维度的子数组。任何
被应用于外层ARRAY构造器的造型会自动传播到所有的内层构造器。
多维数组构造器元素可以是任何得到一个正确种类数组的任何东西，而不仅仅是一个
子-ARRAY结构。例如：
CREATE TABLE arr(f1 int[], f2 int[]);
INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]);
SELECT ARRAY[f1, f2, ‘{{9,10},{11,12}}’::int[]] FROM arr;
array

{{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}}
(1 row)
你可以构造一个空数组，但是因为无法得到一个无类型的数组，你必须显式地把你的空数组
造型成想要的类型。例如：
SELECT ARRAY[]::integer[];
array

{}
(1 row)
也可以从一个子查询的结果构建一个数组。在这种形式中，数组构造器被写为关键
词ARRAY后跟着一个加了圆括号（不是方括号）的子查询。例如：
SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE ‘bytea%’);
array

{2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412,2413}
(1 row)
SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i));
array

{{1,2},{2,4},{3,6},{4,8},{5,10}}
(1 row)
子查询必须返回一个单一列。如果子查询的输出列是非数组类型，结果的一维数组将为该
子查询结果中的每一行有一个元素，并且有一个与子查询的输出列匹配的元素类型。如果
子查询的输出列是一种数组类型，结果将是同类型的一个数组，但是要高一个维度。在这
种情况下，该子查询的所有行必须产生同样维度的数组，否则结果就不会是矩形形式。
用ARRAY构建的一个数组值的下标总是从一开始。更多关于数组的信息，请见第 8.15 节。
4.2.13. 行构造器
一个行构造器是能够构建一个行值（也称作一个组合类型）并用值作为其成员域的表达式。
一个行构造器由关键词ROW、一个左圆括号、用于行的域值的零个或多个表达式（用逗号分
隔）以及最后的一个右圆括号组成。例如：
49
SQL语法
SELECT ROW(1,2.5,‘this is a test’);
当在列表中有超过一个表达式时，关键词ROW是可选的。
一个行构造器可以包括语法rowvalue.，它将被扩展为该行值的元素的一个列表，就像在一
个顶层SELECT列表（见第 8.16.5 节）中使用.时发生的事情一样。例如，如果表t有
列f1和f2，那么这些是相同的：
SELECT ROW(t., 42) FROM t;
SELECT ROW(t.f1, t.f2, 42) FROM t;
注意
在PostgreSQL 8.2 以前，.语法不会在行构造器中被扩展，这样
写ROW(t., 42)会创建一个有两个域的行，其第一个域是另一个行值。新的行
为通常更有用。如果你需要嵌套行值的旧行为，写内层行值时不要用.，例
如ROW(t, 42)。
默认情况下，由一个ROW表达式创建的值是一种匿名记录类型。如果必要，它可以被造型为
一种命名的组合类型 — 或者是一个表的行类型，或者是一种用CREATE TYPE AS创建的组合
类型。为了避免歧义，可能需要一个显式造型。例如：
CREATE TABLE mytable(f1 int, f2 float, f3 text);
CREATE FUNCTION getf1(mytable) RETURNS int AS ‘SELECT $1.f1’ LANGUAGE SQL;
– 不需要造型因为只有一个 getf1() 存在
SELECT getf1(ROW(1,2.5,‘this is a test’));
getf1

1
(1 row)
CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
CREATE FUNCTION getf1(myrowtype) RETURNS int AS ‘SELECT $1.f1’ LANGUAGE SQL;
– 现在我们需要一个造型来指示要调用哪个函数：
SELECT getf1(ROW(1,2.5,‘this is a test’));
ERROR: function getf1(record) is not unique
SELECT getf1(ROW(1,2.5,‘this is a test’)::mytable);
getf1

1
(1 row)
SELECT getf1(CAST(ROW(11,‘this is a test’,2.5) AS myrowtype));
getf1

11
(1 row)
行构造器可以被用来构建存储在一个组合类型表列中的组合值，或者被传递给一个接受组合
参数的函数。还有，可以比较两个行值，或者用IS NULL或IS NOT NULL测试一个行，例如：
50
SQL语法
SELECT ROW(1,2.5,‘this is a test’) = ROW(1, 3, ‘not the same’);
SELECT ROW(table.) IS NULL FROM table; – detect all-null rows
详见第 9.23 节。如第 9.22 节中所讨论的，行构造器也可以被用来与子查询相连接。
4.2.14. 表达式计算规则
子表达式的计算顺序没有被定义。特别地，一个操作符或函数的输入不必按照从左至右或其
他任何固定顺序进行计算。
此外，如果一个表达式的结果可以通过只计算其一部分来决定，那么其他子表达式可能完全
不需要被计算。例如，如果我们写：
SELECT true OR somefunc();
那么somefunc()将（可能）完全不被调用。如果我们写成下面这样也是一样：
SELECT somefunc() OR true;
注意这和一些编程语言中布尔操作符从左至右的“短路”不同。
因此，在复杂表达式中使用带有副作用的函数是不明智的。在WHERE和HAVING子句中依赖副
作用或计算顺序尤其危险，因为在建立一个执行计划时这些子句会被广泛地重新处理。这些
子句中布尔表达式（AND/OR/NOT的组合）可能会以布尔代数定律所允许的任何方式被重组。
当有必要强制计算顺序时，可以使用一个CASE结构（见第 9.17 节）。例如，在一个WHERE子
句中使用下面的方法尝试避免除零是不可靠的：
SELECT … WHERE x > 0 AND y/x > 1.5;
但是这是安全的：
SELECT … WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
一个以这种风格使用的CASE结构将使得优化尝试失败，因此只有必要时才这样做（在这个特
别的例子中，最好通过写y > 1.5x来回避这个问题）。
不过，CASE不是这类问题的万灵药。上述技术的一个限制是，它无法阻止常量子表达式的
提早计算。如第 37.7 节中所述，当查询被规划而不是被执行时，被标记成 IMMUTABLE的函
数和操作符可以被计算。因此
SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;
很可能会导致一次除零失败，因为规划器尝试简化常量子表达式。即便是表中的每一行都
有x > 0（这样运行时永远不会进入到 ELSE分支）也是这样。
虽然这个特别的例子可能看起来愚蠢，没有明显涉及常量的情况可能会发生在函数内执
行的查询中，因为因为函数参数的值和本地变量可以作为常量被插入到查询中用于规划目
的。例如，在PL/pgSQL函数中，使用一个IF-THEN-ELSE语句来保护一种有风险的计算比把
它嵌在一个CASE表达式中要安全得多。
另一个同类型的限制是，一个CASE无法阻止其所包含的聚集表达式的计算，因为在考
虑SELECT列表或HAVING子句中的其他表达式之前，会先计算聚集表达式。例如，下面的查
询会导致一个除零错误，虽然看起来好像已经这种情况加以了保护：
51
SQL语法
SELECT CASE WHEN min(employees) > 0
THEN avg(expenses / employees)
END
FROM departments;
min()和avg()聚集会在所有输入行上并行地计算，因此如果任何行有employees等于零，
在有机会测试 min()的结果之前，就会发生除零错误。取而代之的是，可以使用一
个WHERE或FILTER子句来首先阻止有问题的输入行到达一个聚集函数。
4.3. 调用函数
PostgreSQL允许带有命名参数的函数被使用位置或命名记号法调用。命名记号法对于有大量
参数的函数特别有用，因为它让参数和实际参数之间的关联更明显和可靠。在位置记号法
中，书写一个函数调用时，其参数值要按照它们在函数声明中被定义的顺序书写。在命名记
号法中，参数根据名称匹配函数参数，并且可以以任何顺序书写。对于每一种记法，还要考
虑函数参数类型的效果，这些在第 10.3 节有介绍。
在任意一种记号法中，在函数声明中给出了默认值的参数根本不需要在调用中写出。但是这
在命名记号法中特别有用，因为任何参数的组合都可以被忽略。而在位置记号法中参数只能
从右往左忽略。
PostgreSQL也支持混合记号法，它组合了位置和命名记号法。在这种情况中，位置参数被首
先写出并且命名参数出现在其后。
下列例子将展示所有三种记号法的用法：
CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT
false)
RETURNS text
AS
$KaTeX parse error: Can't use function '$' in math mode at position 21: \dotsECT CASE WHEN $̲3 THEN UPPER($1\dots$
LANGUAGE SQL IMMUTABLE STRICT;
函数concat_lower_or_upper有两个强制参数，a和b。此外，有一个可选的参数uppercase，
其默认值为false。a和b输入将被串接，并且根据uppercase参数被强制为大写或小写形式。
这个函数的剩余细节对这里并不重要（详见第 37 章）。
4.3.1. 使用位置记号
在PostgreSQL中，位置记号法是给函数传递参数的传统机制。一个例子：
SELECT concat_lower_or_upper(‘Hello’, ‘World’, true);
concat_lower_or_upper

HELLO WORLD
(1 row)
所有参数被按照顺序指定。结果是大写形式，因为uppercase被指定为true。另一个例子：
SELECT concat_lower_or_upper(‘Hello’, ‘World’);
52
SQL语法
concat_lower_or_upper

hello world
(1 row)
这里，uppercase参数被忽略，因此它接收它的默认值false，并导致小写形式的输出。在位
置记号法中，参数可以按照从右往左被忽略并且因此而得到默认值。
4.3.2. 使用命名记号
在命名记号法中，每一个参数名都用=> 指定来把它与参数表达式分隔开。例如：
SELECT concat_lower_or_upper(a => ‘Hello’, b => ‘World’);
concat_lower_or_upper

hello world
(1 row)
再次，参数uppercase被忽略，因此它被隐式地设置为false。使用命名记号法的一个优点是
参数可以用任何顺序指定，例如：
SELECT concat_lower_or_upper(a => ‘Hello’, b => ‘World’, uppercase => true);
concat_lower_or_upper

HELLO WORLD
(1 row)
SELECT concat_lower_or_upper(a => ‘Hello’, uppercase => true, b => ‘World’);
concat_lower_or_upper

HELLO WORLD
(1 row)
为了向后兼容性，基于 “:=” 的旧语法仍被支持：
SELECT concat_lower_or_upper(a := ‘Hello’, uppercase := true, b := ‘World’);
concat_lower_or_upper

HELLO WORLD
(1 row)
4.3.3. 使用混合记号
混合记号法组合了位置和命名记号法。不过，正如已经提到过的，命名参数不能超越位置参
数。例如：
SELECT concat_lower_or_upper(‘Hello’, ‘World’, uppercase => true);
concat_lower_or_upper

HELLO WORLD
(1 row)
在上述查询中，参数a和b被以位置指定，而uppercase通过名字指定。在这个例子中，这只
增加了一点文档。在一个具有大量带默认值参数的复杂函数中，命名的或混合的记号法可以
节省大量的书写并且减少出错的机会。
53
SQL语法
注意
命名的和混合的调用记号法当前不能在调用聚集函数时使用（但是当聚集函数
被用作窗口函数时它们可以被使用）。
54
第 5 章数据定义
本章包含了如何创建用来保存数据的数据库结构。在一个关系型数据库中，原始数据被存储
在表中，因此本章的主要工作就是解释如何创建和修改表，以及哪些特性可以控制何种数据
会被存储在表中。接着，我们讨论表如何被组织成模式，以及如何将权限分配给表。最后，
我们将将简短地介绍其他一些影响数据存储的特性，例如继承、表分区、视图、函数和触发
器。
5.1. 表基础
关系型数据库中的一个表非常像纸上的一张表：它由行和列组成。列的数量和顺序是固定
的，并且每一列拥有一个名字。行的数目是变化的，它反映了在一个给定时刻表中存储的数
据量。SQL并不保证表中行的顺序。当一个表被读取时，表中的行将以非特定顺序出现，除
非明确地指定需要排序。这些将在第 7 章介绍。此外，SQL不会为行分配唯一的标识符，因
此在一个表中可能会存在一些完全相同的行。这是SQL之下的数学模型导致的结果，但并不
是所期望的。稍后在本章中我们将看到如何处理这种问题。
每一列都有一个数据类型。数据类型约束着一组可以分配给列的可能值，并且它为列中存储
的数据赋予了语义，这样它可以用于计算。例如，一个被声明为数字类型的列将不会接受任
何文本串，而存储在这样一列中的数据可以用来进行数学计算。反过来，一个被声明为字符
串类型的列将接受几乎任何一种的数据，它可以进行如字符串连接的操作但不允许进行数学
计算。
PostgreSQL包括了相当多的内建数据类型，可以适用于很多应用。用户也可以定义他们自己
的数据类型。大部分内建数据类型有着显而易见的名称和语义，所以我们将它们的详细解释
放在第 8 章中。一些常用的数据类型是：用于整数的integer；可以用于分数的numeric；用
于字符串的text，用于日期的date，用于一天内时间的time以及可以同时包含日期和时间
的timestamp。
要创建一个表，我们要用到CREATE TABLE命令。在这个命令中我们需要为新表至少指定一个
名字、列的名字及数据类型。例如：
CREATE TABLE my_first_table (
first_column text,
second_column integer
);
这将创建一个名为my_first_table的表，它拥有两个列。第一个列名为first_column且数据
类型为text；第二个列名为second_column且数据类型为integer。表和列的名字遵
循第 4.1.1 节中解释的标识符语法。类型名称通常也是标识符，但是也有些例外。注意列的
列表由逗号分隔并被圆括号包围。
当然，前面的例子是非常不自然的。通常，我们为表和列赋予的名称都会表明它们存储着什
么类别的数据。因此让我们再看一个更现实的例子：
CREATE TABLE products (
product_no integer,
name text,
price numeric
);
（numeric类型能够存储小数部分，典型的例子是金额。）
55
数据定义
提示
当我们创建很多相关的表时，最好为表和列选择一致的命名模式。例如，一种
选择是用单数或复数名词作为表名，每一种都受到一些理论家支持。
一个表能够拥有的列的数据是有限的，根据列的类型，这个限制介于250和1600之间。但
是，极少会定义一个接近这个限制的表，即便有也是一个值的商榷的设计。
如果我们不再需要一个表，我们可以通过使用DROP TABLE命令来移除它。例如：
DROP TABLE my_first_table;
DROP TABLE products;
尝试移除一个不存在的表会引起错误。然而，在SQL脚本中在创建每个表之前无条件地尝试
移除它的做法是很常见的，即使发生错误也会忽略之，因此这样的脚本可以在表存在和不存
在时都工作得很好（如果你喜欢，可以使用DROP TABLE IF EXISTS变体来防止出现错误消
息，但这并非标准SQL）。
如果我们需要修改一个已经存在的表，请参考本章稍后的第 5.6 节。
利用到目前为止所讨论的工具，我们可以创建一个全功能的表。本章的后续部分将集中于为
表定义增加特性来保证数据完整性、安全性或方便。如果你希望现在就去填充你的表，你可
以跳过这些直接去第 6 章。
5.2. 默认值
一个列可以被分配一个默认值。当一个新行被创建且没有为某些列指定值时，这些列将会被
它们相应的默认值填充。一个数据操纵命令也可以显式地要求一个列被置为它的默认值，而
不需要知道这个值到底是什么（数据操纵命令详见第 6 章）。
如果没有显式指定默认值，则默认值是空值。这是合理的，因为空值表示未知数据。
在一个表定义中，默认值被列在列的数据类型之后。例如：
CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99
);
默认值可以是一个表达式，它将在任何需要插入默认值的时候被实时计算（不是表创建
时）。一个常见的例子是为一个timestamp列指定默认值为CURRENT_TIMESTAMP，这样它将得
到行被插入时的时间。另一个常见的例子是为每一行生成一个“序列号” 。这在
PostgreSQL可以按照如下方式实现：
CREATE TABLE products (
product_no integer DEFAULT nextval(‘products_product_no_seq’),
…
);
这里nextval()函数从一个序列对象第 9.16 节）。还有一种特别的速写：
CREATE TABLE products (
56
数据定义
product_no SERIAL,
…
);
SERIAL速写将在第 8.1.4 节进一步讨论。
5.3. 生成列
生成的列是一个特殊的列，它总是从其他列计算而来。因此说，它对于列就像视图对于表一
样。生成列有两种:存储列和虚拟列。存储生成列在写入(插入或更新)时计算，并且像普通
列一样占用存储空间。虚拟生成列不占用存储空间并且在读取时进行计算。如此看来，虚
拟生成列类似于视图，存储生成列类似于物化视图(除了它总是自动更新之外)。
PostgreSQL目前只实现了存储生成列。
建立一个生成列，在 CREATE TABLE中使用 GENERATED ALWAYS AS 子句, 例如:
CREATE TABLE people (
…,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);
必须指定关键字 STORED 以选择存储类型的生成列。更多细节请参见 CREATE TABLE 。
生成列不能被直接写入. 在INSERT 或 UPDATE 命令中, 不能为生成列指定值, 但是可以指
定关键字DEFAULT。
考虑列缺省情况和生成列之间的差异。如果没有提供其他值，列缺省情况下在行被首次
插入时计算一次;生成列则在行每次改变时进行更新，并且不能被取代。列缺省情况下不
能引用表的其他列；生成表达式通常会这样做。列缺省情况下可以使用易失性函数，例
如random()或引用当前时间函数; 而对于生成列这是不允许的。
生成列和涉及生成列的表的定义有几个限制:
• 生成表达式只能使用不可变函数，并且不能使用子查询或以任何方式引用当前行以外的任
何内容。
• 生成表达式不能引用另一个生成列。
• 生成表达式不能引用系统表，除了 tableoid。
• 生成列不能具有列默认或标识定义。
• 生成列不能是分区键的一部分。
• 外部表可以有生成列. 更多细节请参见 CREATE FOREIGN TABLE .
使用生成列的其他注意事项。
• 生成列保留着有别于其下层的基础列的访问权限。因此，可以对其进行排列以便于从生成
列中读取特定的角色，而不是从下层基础列。
• 从概念上讲，生成列在BEFORE 触发器运行后更新。因此，BEFORE 触发器中的基础列所做
的变更将反映在生成列中。但相反，不允许访问BEFORE 触发器中的生成列。
5.4. 约束
数据类型是一种限制能够存储在表中数据类别的方法。但是对于很多应用来说，它们提供的
约束太粗糙。例如，一个包含产品价格的列应该只接受正值。但是没有任何一种标准数据类
57
数据定义
型只接受正值。另一个问题是我们可能需要根据其他列或行来约束一个列中的数据。例如，
在一个包含产品信息的表中，对于每个产品编号应该只有一行。
到目前为止，SQL允许我们在列和表上定义约束。约束让我们能够根据我们的愿望来控制表
中的数据。如果一个用户试图在一个列中保存违反一个约束的数据，一个错误会被抛出。即
便是这个值来自于默认值定义，这个规则也同样适用。
5.4.1. 检查约束
一个检查约束是最普通的约束类型。它允许我们指定一个特定列中的值必须要满足一个布尔
表达式。例如，为了要求正值的产品价格，我们可以使用：
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
如你所见，约束定义就和默认值定义一样跟在数据类型之后。默认值和约束之间的顺序没有
影响。一个检查约束有关键字CHECK以及其后的包围在圆括号中的表达式组成。检查约束表
达式应该涉及到被约束的列，否则该约束也没什么实际意义。
我们也可以给与约束一个独立的名称。这会使得错误消息更为清晰，同时也允许我们在需要
更改约束时能引用它。语法为：
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
要指定一个命名的约束，请在约束名称标识符前使用关键词CONSTRAINT，然后把约束定义放
在标识符之后（如果没有以这种方式指定一个约束名称，系统将会为我们选择一个）。
一个检查约束也可以引用多个列。例如我们存储一个普通价格和一个打折后的价格，而我们
希望保证打折后的价格低于普通价格：
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
前两个约束看起来很相似。第三个则使用了一种新语法。它并没有依附在一个特定的列，而
是作为一个独立的项出现在逗号分隔的列列表中。列定义和这种约束定义可以以混合的顺序
出现在列表中。
我们将前两个约束称为列约束，而第三个约束为表约束，因为它独立于任何一个列定义。列
约束也可以写成表约束，但反过来不行，因为一个列约束只能引用它所依附的那一个列
（PostgreSQL并不强制要求这个规则，但是如果我们希望表定义能够在其他数据库系统中工
作，那就应该遵循它）。上述例子也可以写成：
CREATE TABLE products (
product_no integer,
58
数据定义
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
甚至是：
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0 AND price > discounted_price)
);
这只是口味的问题。
表约束也可以用列约束相同的方法来指定名称：
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);
需要注意的是，一个检查约束在其检查表达式值为真或空值时被满足。因为当任何操作数为
空时大部分表达式将计算为空值，所以它们不会阻止被约束列中的控制。为了保证一个列不
包含控制，可以使用下一节中的非空约束。
注意
PostgreSQL不支持引用表数据以外的要检查的新增或更新的行的CHECK约束。
虽然违反此规则的CHECK约束在简单测试中看起来能工作，它不能保证数据库
不会达到约束条件为假(false)的状态（由于涉及的其他行随后发生了更
改）。这将导致数据库转储和重新加载失败。即使完整的数据库状态与约束
一致，重新加载也可能失败，因为行未按照满足约束的顺序加载。如果可能
的话，使用UNIQUE, EXCLUDE,或 FOREIGN KEY约束以表示跨行和跨表限制。
如果你希望的是在插入行时的时候对其他行进行一次性检查，而不是持续维护
的一致性保证，一个自定义的 trigger 可以用于实现这个功
能。（此方法避免了转储/重新加载问题，因为pg_dump不会重新安装触发器
直到重新加载数据之后，因此不会在转储/重新加载期间强制执行检查。）
注意
PostgreSQL假定CHECK约束的条件是不可变的，也就是说，它们始终为同一输
入行提供相同的结果。这个假设是仅在插入或更新行时,而不是在其他时间检
59
数据定义
查CHECK约束的原因。（上面关于不引用其他表数据的警告实际上是此限制的
特殊情况。）
打破此假设的常见方法的一个示例是在 CHECK表达式中引用用户定义的函数，
然后更改该函数的行为。 PostgreSQL不会禁止那样，但它不会注意到现在表
中是否有行违反了CHECK约束。这将导致后续数据库转储和重新加载失败。处
理此类更改的建议方法是删除约束（使用ALTER TABLE），调整函数定义，然
后重新添加约束，从而对所有表行进行重新检查。
5.4.2. 非空约束
一个非空约束仅仅指定一个列中不会有空值。语法例子：
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
一个非空约束总是被写成一个列约束。一个非空约束等价于创建一个检查约束CHECK
(column_name IS NOT NULL)，但在PostgreSQL中创建一个显式的非空约束更高效。这种方式
创建的非空约束的缺点是我们无法为它给予一个显式的名称。
当然，一个列可以有多于一个的约束，只需要将这些约束一个接一个写出：
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0)
);
约束的顺序没有关系，因为并不需要决定约束被检查的顺序。
NOT NULL约束有一个相反的情况：NULL约束。这并不意味着该列必须为空，进而肯定是无用
的。相反，它仅仅选择了列可能为空的默认行为。SQL标准中并不存在NULL约束，因此它不
能被用于可移植的应用中（PostgreSQL中加入它是为了和某些其他数据库系统兼容）。但是
某些用户喜欢它，因为它使得在一个脚本文件中可以很容易的进行约束切换。例如，初始时
我们可以：
CREATE TABLE products (
product_no integer NULL,
name text NULL,
price numeric NULL
);
然后可以在需要的地方插入NOT关键词。
提示
在大部分数据库中多数列应该被标记为非空。
5.4.3. 唯一约束
60
数据定义
唯一约束保证\在一列中或者一组列中保存的数据在表中所有行间是唯一的。写成一个列约
束的语法是：
CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric
);
写成一个表约束的语法是：
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);
当写入表约束时。
要为一组列定义一个唯一约束，把它写作一个表级约束，列名用逗号分隔：
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
这指定这些列的组合值在整个表的范围内是唯一的，但其中任意一列的值并不需要是（一般
也不是）唯一的。
我们可以通常的方式为一个唯一索引命名：
CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);
增加一个唯一约束会在约束中列出的列或列组上自动创建一个唯一B-tree索引。只覆盖某些
行的唯一性限制不能被写为一个唯一约束，但可以通过创建一个唯一的部分索引来强制这种
限制。
通常，如果表中有超过一行在约束所包括列上的值相同，将会违反唯一约束。但是在这种比
较中，两个空值被认为是不同的。这意味着即便存在一个唯一约束，也可以存储多个在至少
一个被约束列中包含空值的行。这种行为符合SQL标准，但我们听说一些其他SQL数据库可能
不遵循这个规则。所以在开发需要可移植的应用时应注意这一点。
5.4.4. 主键
一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列。这要求那些值都是唯
一的并且非空。因此，下面的两个表定义接受相同的数据：
61
数据定义
CREATE TABLE products (
product_no integer UNIQUE NOT NULL,
name text,
price numeric
);
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
主键也可以包含多于一个列，其语法和唯一约束相似：
CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
增加一个主键将自动在主键中列出的列或列组上创建一个唯一B-tree索引。并且会强制这些
列被标记为NOT NULL。
一个表最多只能有一个主键（可以有任意数量的唯一和非空约束，它们可以达到和主键几乎
一样的功能，但只能有一个被标识为主键）。关系数据库理论要求每一个表都要有一个主
键。但PostgreSQL中并未强制要求这一点，但是最好能够遵循它。
主键对于文档和客户端应用都是有用的。例如，一个允许修改行值的 GUI 应用可能需要知道
一个表的主键，以便能唯一地标识行。如果定义了主键，数据库系统也有多种方法来利用主
键。例如，主键定义了外键要引用的默认目标列。
5.4.5. 外键
一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。我们说
这维持了两个关联表之间的引用完整性。
例如我们有一个使用过多次的产品表：
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
让我们假设我们还有一个存储这些产品订单的表。我们希望保证订单表中只包含真正存在的
产品的订单。因此我们在订单表中定义一个引用产品表的外键约束：
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
现在就不可能创建包含不存在于产品表中的product_no值（非空）的订单。
62
数据定义
我们说在这种情况下，订单表是引用表而产品表是被引用表。相应地，也有引用和被引用列
的说法。
我们也可以把上述命令简写为：
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products,
quantity integer
);
因为如果缺少列的列表，则被引用表的主键将被用作被引用列。
一个外键也可以约束和引用一组列。照例，它需要被写成表约束的形式。下面是一个例子：
CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
当然，被约束列的数量和类型应该匹配被引用列的数量和类型。
按照前面的方式，我们可以为一个外键约束命名。
一个表可以有超过一个的外键约束。这被用于实现表之间的多对多关系。例如我们有关于产
品和订单的表，但我们现在希望一个订单能包含多种产品（这在上面的结构中是不允许
的）。我们可以使用这种表结构：
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
…
);
CREATE TABLE order_items (
product_no integer REFERENCES products,
order_id integer REFERENCES orders,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
注意在最后一个表中主键和外键之间有重叠。
我们知道外键不允许创建与任何产品都不相关的订单。但如果一个产品在一个引用它的订单
创建之后被移除会发生什么？SQL允许我们处理这种情况。直观上，我们有几种选项：
• 不允许删除一个被引用的产品
• 同时也删除引用产品的订单
63
数据定义
• 其他？
为了说明这些，让我们在上面的多对多关系例子中实现下面的策略：当某人希望移除一个仍
然被一个订单引用（通过order_items）的产品时，我们组织它。如果某人移除一个订单，
订单项也同时被移除：
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
…
);
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
限制删除或者级联删除是两种最常见的选项。RESTRICT阻止删除一个被引用的行。NO
ACTION表示在约束被检察时如果有任何引用行存在，则会抛出一个错误，这是我们没有指定
任何东西时的默认行为（这两种选择的本质不同在于NO ACTION允许检查被推迟到事务的最
后，而RESTRICT则不会）。CASCADE指定当一个被引用行被删除后，引用它的行也应该被自
动删除。还有其他两种选项：SET NULL和SET DEFAULT。这些将导致在被引用行被删除后，引
用行中的引用列被置为空值或它们的默认值。注意这些并不会是我们免于遵守任何约束。例
如，如果一个动作指定了SET DEFAULT，但是默认值不满足外键约束，操作将会失败。
与ON DELETE相似，同样有ON UPDATE可以用在一个被引用列被修改（更新）的情况，可选的
动作相同。在这种情况下，CASCADE意味着被引用列的更新值应该被复制到引用行中。
正常情况下，如果一个引用行的任意一个引用列都为空，则它不需要满足外键约束。如果在
外键定义中加入了MATCH FULL，一个引用行只有在它的所有引用列为空时才不需要满足外键
约束（因此空和非空值的混合肯定会导致MATCH FULL约束失败）。如果不希望引用行能够避
开外键约束，将引用行声明为NOT NULL。
一个外键所引用的列必须是一个主键或者被唯一约束所限制。这意味着被引用列总是拥有一
个索引（位于主键或唯一约束之下的索引），因此在其上进行的一个引用行是否匹配的检查
将会很高效。由于从被引用表中DELETE一行或者UPDATE一个被引用列将要求对引用表进行扫
描以得到匹配旧值的行，在引用列上建立合适的索引也会大有益处。由于这种做法并不是必
须的，而且创建索引也有很多种选择，所以外键约束的定义并不会自动在引用列上创建索
引。
更多关于更新和删除数据的信息请见第 6 章。外键约束的语法描述请参考CREATE TABLE。
5.4.6. 排他约束
排他约束保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操
作符比较将会返回否或空值。语法是：
CREATE TABLE circles (
c circle,
64
数据定义
EXCLUDE USING gist (c WITH &&)
);
详见CREATE TABLE … CONSTRAINT … EXCLUDE。
增加一个排他约束将在约束声明所指定的类型上自动创建索引。
5.5. 系统列
每一个表都拥有一些由系统隐式定义的system columns。因此，这些列的名字不能像用户定
义的列一样使用（注意这种限制与名称是否为关键词没有关系，即便用引号限定一个名称也
无法绕过这种限制）。事实上用户不需要关心这些列，只需要知道它们存在即可。
tableoid
包含这一行的表的OID。该列是特别为从继承层次（见第 5.10 节）中选择的查询而准
备，因为如果没有它将很难知道一行来自于哪个表。tableoid可以与pg_class的oid列进
行连接来获得表的名称。
xmin
插入该行版本的事务身份（事务ID）。一个行版本是一个行的一个特别版本，对一个逻
辑行的每一次更新都将创建一个新的行版本。
cmin
插入事务中的命令标识符（从0开始）。
xmax
删除事务的身份（事务ID），对于未删除的行版本为0。对于一个可见的行版本，该列值
也可能为非零。这通常表示删除事务还没有提交，或者一个删除尝试被回滚。
cmax
删除事务中的命令标识符，或者为0。
ctid
行版本在其表中的物理位置。注意尽管ctid可以被用来非常快速地定位行版本，但是一
个行的ctid会在被更新或者被VACUUM FULL移动时改变。因此，ctid不能作为一个长期行
标识符。应使用主键来标识逻辑行。
事务标识符也是32位量。在一个历时长久的数据库中事务ID同样会绕回。但如果采取适当的
维护过程，这不会是一个致命的问题，详见第 24 章。但是，长期（超过10亿个事务）依赖
事务ID的唯一性是不明智的。
命令标识符也是32位量。这对一个事务中包含的SQL命令设置了一个硬极限： 232（40亿）。
在实践中，该限制并不是问题 — 注意该限制只是针对SQL命令的数目而不是被处理的行数。
同样，只有真正修改了数据库内容的命令才会消耗一个命令标识符。
5.6. 修改表
当我们已经创建了一个表并意识到犯了一个错误或者应用需求发生改变时，我们可以移除表
并重新创建它。但如果表中已经被填充数据或者被其他数据库对象引用（例如有一个外键约
束），这种做法就显得很不方便。因此，PostgreSQL提供了一族命令来对已有的表进行修
改。注意这和修改表中所包含的数据是不同的，这里要做的是对表的定义或者说结构进行修
改。
65
数据定义
利用这些命令，我们可以：
• 增加列
• 移除列
• 增加约束
• 移除约束
• 修改默认值
• 修改列数据类型
• 重命名列
• 重命名表
所有这些动作都由ALTER TABLE命令执行，其参考页面中包含更详细的信息。
5.6.1. 增加列
要增加一个列，可以使用这样的命令：
ALTER TABLE products ADD COLUMN description text;
新列将被默认值所填充（如果没有指定DEFAULT子句，则会填充空值）。
提示
从 PostgreSQL 11开始，添加一个具有常量默认值的列不再意味着在执
行ALTER TABLE 语句时需要更新表的每一行。相反，默认值将在下次访问该
行时返回，并在表被重写时应用，从而使得ALTER TABLE即使在大表上也非常
快。
但是，如果默认值是可变的（例如clock_timestamp()），则每一行需要
被ALTER TABLE被执行时计算的值更新。为避免潜在的长时间的更新操作，特
别是如果你想要用大多数非默认值填充列，那么最好添加没有默认值的列，再
用 UPDATE插入正确的值，然后按照下面所述添加任何期望的默认值。
也可以同时为列定义约束，语法：
ALTER TABLE products ADD COLUMN description text CHECK (description <> ‘’);
事实上CREATE TABLE中关于一列的描述都可以应用在这里。记住不管怎样，默认值必须满足
给定的约束，否则ADD将会失败。也可以先将新列正确地填充好，然后再增加约束（见后
文）。
5.6.2. 移除列
为了移除一个列，使用如下的命令：
ALTER TABLE products DROP COLUMN description;
列中的数据将会消失。涉及到该列的表约束也会被移除。然而，如果该列被另一个表的外键
所引用，PostgreSQL不会安静地移除该约束。我们可以通过增加CASCADE来授权移除任何依
赖于被删除列的所有东西：
ALTER TABLE products DROP COLUMN description CASCADE;
关于这个操作背后的一般性机制请见第 5.14 节。
66
数据定义
5.6.3. 增加约束
为了增加一个约束，可以使用表约束的语法，例如：
ALTER TABLE products ADD CHECK (name <> ‘’);
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES
product_groups;
要增加一个不能写成表约束的非空约束，可使用语法：
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
该约束会立即被检查，所以表中的数据必须在约束被增加之前就已经符合约束。
5.6.4. 移除约束
为了移除一个约束首先需要知道它的名称。如果在创建时已经给它指定了名称，那么事情就
变得很容易。否则约束的名称是由系统生成的，我们必须先找出这个名称。psql的命令\d
表名将会对此有所帮助，其他接口也会提供方法来查看表的细节。因此命令是：
ALTER TABLE products DROP CONSTRAINT some_name;
（如果处理的是自动生成的约束名称，如 $KaTeX parse error: Undefined control sequence: \l at position 4425: …ATABASE CTc Tc \̲l̲ ̲DOMAIN U U \dD+…$ user", public
第一个元素说明一个和当前用户同名的模式会被搜索。如果不存在这个模式，该项将被忽
略。第二个元素指向我们已经见过的公共模式。
搜索路径中的第一个模式是创建新对象的默认存储位置。这就是默认情况下对象会被创建在
公共模式中的原因。当对象在任何其他没有模式限定的环境中被引用（表修改、数据修改或
查询命令）时，搜索路径将被遍历直到一个匹配对象被找到。因此，在默认配置中，任何非
限定访问将只能指向公共模式。
要把新模式放在搜索路径中，我们可以使用：
SET search_path TO myschema,public;
（我们在这里省略了 $user，因为我们并不立即需要它）。然后我们可以删除该表而无需使用方案进行限定： DROP TABLE mytable; 同样，由于myschema是路径中的第一个元素，新对象会被默认创建在其中。我们也可以这样写： SET search_path TO myschema; 这样我们在没有显式限定时再也不必去访问公共模式了。公共模式没有什么特别之处，它只是默认存在而已，它也可以被删除。其他操作模式搜索路径的方法请见第 9.25 节。搜索路径对于数据类型名称、函数名称和操作符名称的作用与表名一样。数据类型和函数名称可以使用和表名完全相同的限定方式。如果我们需要在一个表达式中写一个限定的操作符名称，我们必须写成一种特殊的形式： OPERATOR(schema.operator) 这是为了避免句法歧义。例如： SELECT 3 OPERATOR(pg_catalog.+) 4; 实际上我们通常都会依赖于搜索路径来查找操作符，因此没有必要去写如此“丑陋”的东西。 5.9.4. 模式和权限默认情况下，用户不能访问不属于他们的方案中的任何对象。要允许这种行为，模式的拥有者必须在该模式上授予USAGE权限。为了允许用户使用方案中的对象，可能还需要根据对象授予额外的权限。一个用户也可以被允许在其他某人的模式中创建对象。要允许这种行为，模式上的CREATE权限必须被授予。注意在默认情况下，所有人都拥有在public模式上的CREATE和USAGE权限。 80 数据定义这使得用户能够连接到一个给定数据库并在它的public模式中创建对象。回收这一特权的使用模式调用： REVOKE CREATE ON SCHEMA public FROM PUBLIC; （第一个“public”是方案，第二个“public”指的是“每一个用户”。第一种是一个标识符，第二种是一个关键词，所以两者的大小写不同。请回想第 4.1.1 节中的指导方针。） 5.9.5. 系统目录模式除public和用户创建的模式之外，每一个数据库还包括一个pg_catalog模式，它包含了系统表和所有内建的数据类型、函数以及操作符。pg_catalog总是搜索路径的一个有效部分。如果没有在路径中显式地包括该模式，它将在路径中的模式之前被搜索。这保证了内建的名称总是能被找到。然而，如果我们希望用用户定义的名称重载内建的名称，可以显式的将pg_catalog放在搜索路径的末尾。由于系统表名称以pg_开头，最好还是避免使用这样的名称，以避免和未来新版本中可能出现的系统表名发生冲突。系统表将继续采用以pg_开头的方式，这样它们不会与非限制的用户表名称冲突。 5.9.6. 使用模式模式能够以多种方式组织数据.secure schema usage pattern防止不受信任的用户更改其他用户查询的行为。当数据库不使用安全模式使用方式时，希望安全地查询该数据库的用户将在每个会话开始时采取保护操作。具体的说，他们将通过设置search_path到空字符串或在其它情况下从search_path中删除非超级用户可写的模式来开始每个会话。默认配置可以很容易的支持一些使用模式。 • 将普通用户约束在其私有的方案中。要实现这一点，发出REVOKE CREATE ON SCHEMA public FROM PUBLIC，并且为每一个用户创建一个用其用户名命名的方案。回想一下以$ user开头的默认搜索路径，该路径解析为
用户名。因此，如果每个用户都有单独的模式，则默认情况下他们访问自己的模
式。在不受信任的用户已经登录的数据库中采用此模式后，请考虑审计名字类似于模
式pg_catalog中的对象的公共模式。此方式是一种安全模式的使用方式，除非不受信任
的用户是数据库所有者或拥有CREATEROLE权限，在这种情况下没有安全模式使用方式存
在。
• 从默认搜索路径中删除公共模式，通过修改postgresql.conf或通过发出ALTER ROLE ALL
SET search_path =“$user”。每一个都保留在公共模式中创建对象的能力，但是只有符合
资格的名称才会选择这些对象。虽然符合资格的表引用是可以的，但是要调用公共模式
中的函数will be unsafe or unreliable。如果在公共模式中创建函数或扩展，请改用第
一个方式。否则，与第一个模式一样，这是安全的，除非不受信任的用户是数据库所有
者或拥有CREATEROLE权限。
• 保持默认。所有用户都隐式地访问公共模式。这模拟了方案根本不可用的情况，可以用于
从无模式感知的世界平滑过渡。但是，这绝不是一个安全的模式。只有当数据库仅有单
个用户或者少数相互信任的用户时，才可以接受。
对于任何一种模式，为了安装共享的应用（所有人都要用其中的表，第三方提供的额外函
数，等等），可把它们放在单独的方案中。记住授予适当的特权以允许其他用户访问它们。
然后用户可以通过以方案名限定名称的方式来引用这些额外的对象，或者他们可以把额外的
方案放在自己的搜索路径中。
5.9.7. 可移植性
在SQL标准中，在由不同用户拥有的同一个模式中的对象是不存在的。此外，某些实现不允
许创建与拥有者名称不同名的模式。事实上，在那些仅实现了标准中基本模式支持的数据库
81
数据定义
中，模式和用户的概念是等同的。因此，很多用户认为限定名称实际上是
由user_name.table_name组成的。如果我们为每一个用户都创建了一个模式，PostgreSQL实
际也是这样认为的。
同样，在SQL标准中也没有public模式的概念。为了最大限度的与标准一致，我们不应使用
（甚至是删除）public模式。
当然，某些SQL数据库系统可能根本没有实现方案，或者提供允许跨数据库访问的名字空
间。如果需要使用这样一些系统，最好不要使用方案。
5.10. 继承
PostgreSQL实现了表继承，这对数据库设计者来说是一种有用的工具（SQL:1999及其后的版
本定义了一种类型继承特性，但和这里介绍的继承有很大的不同）。
让我们从一个例子开始：假设我们要为城市建立一个数据模型。每一个州有很多城市，但是
只有一个首府。我们希望能够快速地检索任何特定州的首府城市。这可以通过创建两个表来
实现：一个用于州首府，另一个用于不是首府的城市。然而，当我们想要查看一个城市的数
据（不管它是不是一个首府）时会发生什么？继承特性将有助于解决这个问题。我们可以
将capitals表定义为继承自cities表：
CREATE TABLE cities (
name text,
population float,
altitude int -# in feet
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
在这种情况下，capitals表继承了它的父表cities的所有列。州首府还有一个额外的
列state用来表示它所属的州。
在PostgreSQL中，一个表可以从0个或者多个其他表继承，而对一个表的查询则可以引用一
个表的所有行或者该表的所有行加上它所有的后代表。默认情况是后一种行为。例如，下面
的查询将查找所有海拔高于500尺的城市的名称，包括州首府：
SELECT name, altitude
FROM cities
WHERE altitude > 500;
对于来自PostgreSQL教程（见第 2.1 节）的例子数据，它将返回：
name | altitude
-#-#-#-#-#-±#-#-#-#-#
Las Vegas | 2174
Mariposa | 1953
Madison | 845
在另一方面，下面的查询将找到海拔超过500尺且不是州首府的所有城市：
SELECT name, altitude
FROM ONLY cities
82
数据定义
WHERE altitude > 500;
name | altitude
-#-#-#-#-#-±#-#-#-#-#
Las Vegas | 2174
Mariposa | 1953
这里的ONLY关键词指示查询只被应用于cities上，而其他在继承层次中位于cities之下的其
他表都不会被该查询涉及。很多我们已经讨论过的命令（如SELECT、UPDATE和DELETE）都支
持ONLY关键词。
我们也可以在表名后写上一个来显式地将后代表包括在查询范围内：
SELECT name, altitude
FROM cities
WHERE altitude > 500;
写*不是必需的，因为这种行为总是默认的。不过，为了兼容可以修改默认值的较老版本，
现在仍然支持这种语法。
在某些情况下，我们可能希望知道一个特定行来自于哪个表。每个表中的系统列tableoid可
以告诉我们行来自于哪个表：
SELECT c.tableoid, c.name, c.altitude
FROM cities c
WHERE c.altitude > 500;
将会返回：
tableoid | name | altitude
-#-#-#-#-#±#-#-#-#-#-±#-#-#-#-#
139793 | Las Vegas | 2174
139793 | Mariposa | 1953
139798 | Madison | 845
（如果重新生成这个结果，可能会得到不同的OID数字。）通过与pg_class进行连接可以看
到实际的表名：
SELECT p.relname, c.name, c.altitude
FROM cities c, pg_class p
WHERE c.altitude > 500 AND c.tableoid = p.oid;
将会返回：
relname | name | altitude
-#-#-#-#-#±#-#-#-#-#-±#-#-#-#-#
cities | Las Vegas | 2174
cities | Mariposa | 1953
capitals | Madison | 845
另一种得到同样效果的方法是使用regclass别名类型，它将象征性地打印出表的 OID：
SELECT c.tableoid::regclass, c.name, c.altitude
FROM cities c
83
数据定义
WHERE c.altitude > 500;
继承不会自动地将来自INSERT或COPY命令的数据传播到继承层次中的其他表中。在我们的例
子中，下面的INSERT语句将会失败：
INSERT INTO cities (name, population, altitude, state)
VALUES (‘Albany’, NULL, NULL, ‘NY’);
我们也许希望数据能被以某种方式被引入到capitals表中，但是这不会发生：INSERT总是向
指定的表中插入。在某些情况下，可以通过使用一个规则（见第 40 章）来将插入动作重定
向。但是这对上面的情况并没有帮助，因为cities表根本就不包含state列，因而这个命令
将在触发规则之前就被拒绝。
父表上的所有检查约束和非空约束都将自动被它的后代所继承，除非显式地指定了NO
INHERIT子句。其他类型的约束（唯一、主键和外键约束）则不会被继承。
一个表可以从超过一个的父表继承，在这种情况下它拥有父表们所定义的列的并集。任何定
义在子表上的列也会被加入到其中。如果在这个集合中出现重名列，那么这些列将被“合
并”，这样在子表中只会有一个这样的列。重名列能被合并的前提是这些列必须具有相同的
数据类型，否则会导致错误。可继承的检查约束和非空约束会以类似的方式被合并。例如，
如果合并成一个合并列的任一列定义被标记为非空，则该合并列会被标记为非空。如果检查
约束的名称相同，则他们会被合并，但如果它们的条件不同则合并会失败。
表继承通常是在子表被创建时建立，使用CREATE TABLE语句的INHERITS子句。一个已经被创
建的表也可以另外一种方式增加一个新的父亲关系，使用ALTER TABLE的INHERIT变体。要这
样做，新的子表必须已经包括和父表相同名称和数据类型的列。子表还必须包括和父表相同
的检查约束和检查表达式。相似地，一个继承链接也可以使用ALTER TABLE的 NO INHERIT变
体从一个子表中移除。动态增加和移除继承链接可以用于实现表划分（见第 5.11 节）。
一种创建一个未来将被用做子女的新表的方法是在CREATE TABLE中使用LIKE子句。这将创建
一个和源表具有相同列的新表。如果源表上定义有任何CHECK约束，LIKE的INCLUDING
CONSTRAINTS选项可以用来让新的子表也包含和父表相同的约束。
当有任何一个子表存在时，父表不能被删除。当子表的列或者检查约束继承于父表时，它们
也不能被删除或修改。如果希望移除一个表和它的所有后代，一种简单的方法是使
用CASCADE选项删除父表（见第 5.14 节）。
ALTER TABLE将会把列的数据定义或检查约束上的任何变化沿着继承层次向下传播。同样，
删除被其他表依赖的列只能使用CASCADE选项。ALTER TABLE对于重名列的合并和拒绝遵循
与CREATE TABLE同样的规则。
继承的查询仅在附表上执行访问权限检查。例如，在cities表上授予UPDATE权限也隐含着通
过cities访问时在capitals表中更新行的权限。这保留了数据（也）在父表中的样子。但
是如果没有额外的授权，则不能直接更新capitals表。此规则的两个例外是TRUNCATE 和
LOCK TABLE，总是检查子表的权限，不管它们是直接处理还是通过在父表上执行的那些命令
递归处理。
以类似的方式，父表的行安全性策略（见第 5.8 节）适用于继承查询期间来自于子表的
行。只有当子表在查询中被明确提到时，其策略（如果有）才会被应用，在那种情况下，
附着在其父表上的任何策略都会被忽略。
外部表（见第 5.12 节）也可以是继承层次中的一部分，即可以作为父表也可以作为子表，
就像常规表一样。如果一个外部表是继承层次的一部分，那么任何不被该外部表支持的操
作也不被整个层次所支持。
5.10.1. 警告
注意并非所有的SQL命令都能工作在继承层次上。用于数据查询、数据修改或模式修改（例
如SELECT、UPDATE、DELETE、大部分ALTER TABLE的变体，但INSERT或ALTER TABLE …
84
数据定义
RENAME不在此列）的命令会默认将子表包含在内并且支持ONLY记号来排除子表。负责数据库
维护和调整的命令（如REINDEX、VACUUM）只工作在独立的、物理的表上并且不支持在继承
层次上的递归。每个命令相应的行为请参见它们的参考页（SQL 命令）。
继承特性的一个严肃的限制是索引（包括唯一约束）和外键约束值应用在单个表上而非它们
的继承子女。在外键约束的引用端和被引用端都是这样。因此，按照上面的例子：
• 如果我们声明cities.name为UNIQUE或者PRIMARY KEY，这将不会阻止capitals表中拥有
和cities中城市同名的行。而且这些重复的行将会默认显示在cities的查询中。事实
上，capitals在默认情况下是根本不能拥有唯一约束的，并且因此能够包含多个同名的
行。我们可以为capitals增加一个唯一约束，但这无法阻止相对于cities的重复。
• 相似地，如果我们指定cities.name REFERENCES某个其他表，该约束不会自动地传播
到capitals。在此种情况下，我们可以变通地在capitals上手工创建一个相同
的REFERENCES约束。
• 指定另一个表的列REFERENCES cities(name)将允许其他表包含城市名称，但不会包含首
府名称。这对于这个例子不是一个好的变通方案。
某些未为继承层次结构实现的功能是为声明性分区实现的。在决定使用旧继承进行分区是否
对应用程序有用时，需要非常小心。
5.11. 表分区
PostgreSQL支持基本的表划分。本小节介绍为何以及怎样把划分实现为数据库设计的一部
分。
5.11.1. 概述
划分指的是将逻辑上的一个大表分成一些小的物理上的片。划分有很多益处：
• 在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数
几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分
更有可能被放在内存中。
• 当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散
到整个表上的索引和随机访问，这样可以改善性能。
• 如果批量操作的需求是在分区设计时就规划好的，则批量装载和删除可以通过增加或者去
除分区来完成。执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE删除一个分区远
快于批量操作。这些命令也完全避免了批量DELETE导致的VACUUM开销。
• 很少使用的数据可以被迁移到便宜且较慢的存储介质上。
当一个表非常大时，划分所带来的好处是非常值得的。一个表何种情况下会从划分获益取决
于应用，一个经验法则是当表的尺寸超过了数据库服务器物理内存时，划分会为表带来好
处。
PostgreSQL对下列分区形式提供了内建支持：
范围划分
表被根据一个关键列或一组列划分为“范围”，不同的分区的范围之间没有重叠。例
如，我们可以根据日期范围划分，或者根据特定业务对象的标识符划分。
列表划分
通过显式地列出每一个分区中出现的键值来划分表。
85
数据定义
哈希分区
通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键
的值除以为其指定的模数将产生为其指定的余数。
如果你的应用需要使用上面所列之外的分区形式，可以使用诸如继承和UNION ALL视图之类
的替代方法。这些方法很灵活，但是却缺少内建声明式分区的一些性能优势。
5.11.2. 声明式划分
PostgreSQL提供了一种方法指定如何把一个表划分成称为分区的片段。被划分的表被称作分
区表。这种说明由分区方法以及要被用作分区键的列或者表达式列表组成。
所有被插入到分区表的行将被基于分区键的值路由到分区中。每个分区都有一个由其分区边
界定义的数据子集。当前支持的分区方法是范围、列表以及哈希。
分区本身也可能被定义为分区表，这种用法被称为子分区。分区可以有自己的与其他分区不
同的索引、约束以及默认值。创建分区表及分区的更多细节请见CREATE TABLE。
无法把一个常规表转换成分区表，反之亦然。不过，可以把一个包含数据的常规表或者分区
表作为分区加入到另一个分区表，或者从分区表中移走一个分区并且把它变成一个独立的
表。有关ATTACH PARTITION和DETACH PARTITION子命令的内容请见ALTER TABLE。
个体分区在内部以继承的方式链接到分区表，不过无法对声明式分区表或其分区使用继承的
某些一般特性（下文讨论）。例如，分区不能有除其所属分区表之外的父表，一个常规表也
不能从分区表继承使得后者成为其父表。这意味着分区表及其分区不会参与到与常规表的继
承关系中。由于分区表及其分区组成的分区层次仍然是一种继承层次，所有第 5.10 节中所
述的继承的普通规则也适用，不过有一些例外，尤其是：
• 分区表的CHECK约束和NOT NULL约束总是会被其所有的分区所继承。不允许在分区表上创
建标记为NO INHERIT的CHECK约束。
• 只要分区表中不存在分区，则支持使用ONLY仅在分区表上增加或者删除约束。一旦分区存
在，那样做就会导致错误，因为当分区存在时是不支持仅在分区表上增加或删除约束的。
不过，分区表本身上的约束可以被增加（如果它们不出现在父表中）和删除。
• 由于分区表并不直接拥有任何数据，尝试在分区表上使用TRUNCATE ONLY将总是返回错
误。
• 分区不能有在父表中不存在的列。在使用CREATE TABLE创建分区时不能指定列，在事后使
用ALTER TABLE时也不能为分区增加列。只有当表的列正好匹配父表时，才能使用ALTER
TABLE … ATTACH PARTITION将它作为分区加入。
• 如果NOT NULL约束在父表中存在，那么就不能删除分区的列上的对应的NOT NULL约束。
分区也可以是外部表，不过它们有一些普通表没有的限制，详情请见CREATE FOREIGN
TABLE。
更新行的分区键可能导致它满足另一个不同的分区的分区边界，进而被移动到那个分区中。
5.11.2.1. 例子
假定我们正在为一个大型的冰激凌公司构建数据库。该公司每天测量最高温度以及每个区域
的冰激凌销售情况。概念上，我们想要一个这样的表：
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
86
数据定义
);
我们知道大部分查询只会访问上周的、上月的或者上季度的数据，因为这个表的主要用途是
为管理层准备在线报告。为了减少需要被存放的旧数据量，我们决定只保留最近3年的数
据。在每个月的开始我们将去除掉最早的那个月的数据。在这种情况下我们可以使用分区技
术来帮助我们满足对measurement表的所有不同需求。
要在这种情况下使用声明式分区，可采用下面的步骤：

通过指定PARTITION BY子句把measurement表创建为分区表，该子句包括分区方法（这个
例子中是RANGE）以及用作分区键的列列表。
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
你可能需要决定在分区键中使用多列进行范围分区。当然，这通常会导致较大数量的分
区，其中每一个个体都比较小。另一方面，使用较少的列可能会导致粗粒度的分区策略得
到较少数量的分区。如果条件涉及这些列中的一部分或者全部，访问分区表的查询将不得
不扫描较少的分区。例如，考虑一个使用列lastname和firstname（按照这样的顺序）作
为分区键进行范围分区的表。
创建分区。每个分区的定义必须指定对应于父表的分区方法和分区键的边界。注意，如果
指定的边界使得新分区的值会与已有分区中的值重叠，则会导致错误。向父表中插入无法
映射到任何现有分区的数据将会导致错误，这种情况下应该手工增加一个合适的分区。
分区以普通PostgreSQL表（或者可能是外部表）的方式创建。可以为每个分区单独指定表
空间和存储参数。
没有必要创建表约束来描述分区的分区边界条件。相反，只要需要引用分区约束时，分区
约束会自动地隐式地从分区边界说明中生成。
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM (‘2006-02-01’) TO (‘2006-03-01’);
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM (‘2006-03-01’) TO (‘2006-04-01’);
…
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
FOR VALUES FROM (‘2007-11-01’) TO (‘2007-12-01’);
CREATE TABLE measurement_y2007m12 PARTITION OF measurement
FOR VALUES FROM (‘2007-12-01’) TO (‘2008-01-01’)
TABLESPACE fasttablespace;
CREATE TABLE measurement_y2008m01 PARTITION OF measurement
FOR VALUES FROM (‘2008-01-01’) TO (‘2008-02-01’)
WITH (parallel_workers = 4)
TABLESPACE fasttablespace;
为了实现子分区，在创建分区的命令中指定PARTITION BY子句，例如：
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM (‘2006-02-01’) TO (‘2006-03-01’)
87
数据定义
PARTITION BY RANGE (peaktemp);
在创建了measurement_y2006m02的分区之后，任何被插入到measurement中且被映射
到measurement_y2006m02的数据（或者直接被插入到measurement_y2006m02的数据，假定
它满足这个分区的分区约束）将被基于peaktemp列进一步重定向
到measurement_y2006m02的一个分区。指定的分区键可以与父亲的分区键重叠，不过在指
定子分区的边界时要注意它接受的数据集合是分区自身边界允许的数据集合的一个子集，
系统不会尝试检查事情情况是否如此。
在分区表的键列上创建一个索引，还有其他需要的索引（键索引并不是必需的，但是大部
分场景中它都能很有帮助）。这会自动在每个分区上创建一个索引，并且后来创建或者附
着的任何分区也将会包含索引。
CREATE INDEX ON measurement (logdate);
确保enable_partition_pruning配置参数在postgresql.conf中没有被禁用。如果被禁
用，查询将不会按照想要的方式被优化。
在上面的例子中，我们会每个月创建一个新分区，因此写一个脚本来自动生成所需的DDL会
更好。
5.11.2.2. 分区维护
通常在初始定义分区表时建立的分区并非保持静态不变。移除旧分区的数据并且为新数据周
期性地增加新分区的需求比比皆是。分区的最大好处之一就是可以通过操纵分区结构来近乎
瞬时地执行这类让人头痛的任务，而不是物理地去除大量数据。
移除旧数据最简单的选择是删除掉不再需要的分区：
DROP TABLE measurement_y2006m02;
这可以非常快地删除数百万行记录，因为它不需要逐个删除每个记录。不过要注意上面的命
令需要在父表上拿到ACCESS EXCLUSIVE锁。
另一种通常更好的选项是把分区从分区表中移除，但是保留它作为一个独立的表：
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
这允许在它被删除之前在其数据上执行进一步的操作。例如，这通常是一种使
用COPY、pg_dump或类似工具备份数据的好时候。这也是把数据聚集成较小的格式、执行其
他数据操作或者运行报表的好时机。
类似地，我们可以增加一个新分区来处理新数据。我们可以在分区表中创建一个空分区，就
像上面创建的初始分区那样：
CREATE TABLE measurement_y2008m02 PARTITION OF measurement
FOR VALUES FROM (‘2008-02-01’) TO (‘2008-03-01’)
TABLESPACE fasttablespace;
另外一种选择是，有时候在分区结构之外创建新表更加方便，然后将它作为一个合适的分
区。这允许先对数据进行装载、检查和转换，然后再让它们出现在分区表中：
CREATE TABLE measurement_y2008m02
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
TABLESPACE fasttablespace;
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
88
数据定义
CHECK ( logdate >= DATE ‘2008-02-01’ AND logdate < DATE ‘2008-03-01’ );
\copy measurement_y2008m02 from ‘measurement_y2008m02’
-# possibly some other data preparation work
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
FOR VALUES FROM (‘2008-02-01’) TO (‘2008-03-01’ );
在运行ATTACH PARTITION命令之前，推荐在要被挂接的表上创建一个CHECK约束来匹配期望
的分区约束。这样，系统将能够跳过扫描来验证隐式分区约束。没有CHECK约束，将扫描表
以验证分区约束，同时对该分区持有ACCESS EXCLUSIVE锁定，并在父表上持有SHARE UPDATE
EXCLUSIVE锁。在完成ATTACH PARTITION后，可能需要删除冗余CHECK约束。
如上所述，可以在分区的表上创建索引，并自动将其应用于整个层次结构。这非常便利，
因为不仅现有分区将变为索引，而且将来创建的任何分区都将变为索引。一个限制是，在
创建这样一个分区索引时，不可能同时使用CONCURRENTLY限定符。为了克服长时间锁，可
以对分区表使用CREATE INDEX ON ONLY ;这样的索引被标记为无效，并且分区不会自动应
用该索引。分区上的索引可以使用CONCURRENTLY分别的创建。然后使用ALTER INDEX …
ATTACH PARTITIONattached到父索引。一旦所有分区的索引附加到父索引，父索引将自动标
记为有效。例如：
CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);
CREATE INDEX measurement_usls_200602_idx
ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
ATTACH PARTITION measurement_usls_200602_idx;
…
该技术也可以与UNIQUE 和PRIMARY KEY 约束一起试用; 当创建约束时隐式创建索引。例如:
ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);
ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
…
5.11.2.3. 限制
分区表有下列限制：
• 没有办法创建跨越所有分区的排除约束，只可能单个约束每个叶子分区。
• 分区表上的惟一约束必须包括所有分区键列。存在此限制是因为PostgreSQL只能每个分区
中分别强制实施唯一性。
• 如果必要，必须在个体分区上定义BEFORE ROW触发器，分区表上不需要。
• 不允许在同一个分区树中混杂临时关系和持久关系。因此，如果分区表是持久的，则其分
区也必须是持久的，反之亦然。在使用临时关系时，分区数的所有成员都必须来自于同一
个会话。
5.11.3. 使用继承实现
虽然内建的声明式分区适合于大部分常见的用例，但还是有一些场景需要更加灵活的方法。
分区可以使用表继承来实现，这能够带来一些声明式分区不支持的特性，例如：
89
数据定义
• 对声明式分区来说，分区必须具有和分区表正好相同的列集合，而在表继承中，子表可以
有父表中没有出现过的额外列。
• 表继承允许多继承。
• 声明式分区仅支持范围、列表以及哈希分区，而表继承允许数据按照用户的选择来划分
（不过注意，如果约束排除不能有效地剪枝子表，查询性能可能会很差）。
• 在使用声明式分区时，一些操作比使用表继承时要求更长的持锁时间。例如，向分区表中
增加分区或者从分区表移除分区要求在父表上取得一个ACCESS EXCLUSIVE锁，而在常规继
承的情况下一个SHARE UPDATE EXCLUSIVE锁就足够了。
5.11.3.1. 例子
我们使用上面用过的同一个measurement表。为了使用继承实现分区，可使用下面的步骤：
创建“主”表，所有的“子”表都将从它继承。这个表将不包含数据。不要在这个表上定
义任何检查约束，除非想让它们应用到所有的子表上。同样，在这个表上定义索引或者唯
一约束也没有意义。对于我们的例子来说，主表是最初定义的measurement表。
创建数个“子”表，每一个都从主表继承。通常，这些表将不会在从主表继承的列集合之
外增加任何列。正如声明性分区那样，这些表就是普通的PostgreSQL表（或者外部表）。
CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
…
CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
为子表增加不重叠的表约束来定义每个分区允许的键值。
典型的例子是：
CHECK ( x = 1 )
CHECK ( county IN ( ‘Oxfordshire’, ‘Buckinghamshire’, ‘Warwickshire’ ))
CHECK ( outletID >= 100 AND outletID < 200 )
确保约束能保证不同子表允许的键值之间没有重叠。设置范围约束的常见错误：
CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )
这是错误的，因为不清楚键值200属于哪一个子表。
像下面这样创建子表会更好：
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE ‘2006-02-01’ AND logdate < DATE ‘2006-03-01’ )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE ‘2006-03-01’ AND logdate < DATE ‘2006-04-01’ )
) INHERITS (measurement);
…
CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE ‘2007-11-01’ AND logdate < DATE ‘2007-12-01’ )
90
数据定义
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE ‘2007-12-01’ AND logdate < DATE ‘2008-01-01’ )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE ‘2008-01-01’ AND logdate < DATE ‘2008-02-01’ )
) INHERITS (measurement);
对于每个子表，在键列上创建一个索引，以及任何想要的其他索引。
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
我们希望我们的应用能够使用INSERT INTO measurement …并且数据将被重定向到合适的
分区表。我们可以通过为主表附加一个合适的触发器函数来实现这一点。如果数据将只被
增加到最后一个分区，我们可以使用一个非常简单的触发器函数：
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $BEGIN INSERT INTO measurement_y2008m01 VALUES (NEW.*); RETURN NULL; END;$
LANGUAGE plpgsql;
完成函数创建后，我们创建一个调用该触发器函数的触发器：
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();
我们必须在每个月重新定义触发器函数，这样它才会总是指向当前的子表。而触发器的定
义则不需要被更新。
我们也可能希望插入数据时服务器会自动地定位应该加入数据的子表。我们可以通过一个
更复杂的触发器函数来实现之，例如：
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $BEGIN IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ... ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN INSERT INTO measurement_y2008m01 VALUES (NEW.*); ELSE 91 数据定义 RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END;$
LANGUAGE plpgsql;
触发器的定义和以前一样。注意每一个IF测试必须准确地匹配它的子表的CHECK约束。
当该函数比单月形式更加复杂时，并不需要频繁地更新它，因为可以在需要的时候提前加
入分支。
注意
在实践中，如果大部分插入都会进入最新的子表，最好先检查它。为了简
洁，我们为触发器的检查采用了和本例中其他部分一致的顺序。
把插入重定向到一个合适的子表中的另一种不同方法是在主表上设置规则而不是触发器。
例如：
CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
( logdate >= DATE ‘2006-02-01’ AND logdate < DATE ‘2006-03-01’ )
DO INSTEAD
INSERT INTO measurement_y2006m02 VALUES (NEW.);
…
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
( logdate >= DATE ‘2008-01-01’ AND logdate < DATE ‘2008-02-01’ )
DO INSTEAD
INSERT INTO measurement_y2008m01 VALUES (NEW.);
规则的开销比触发器大很多，但是这种开销是每个查询只有一次，而不是每行一次，因此
这种方法可能对批量插入的情况有优势。不过，在大部分情况下，触发器方法将提供更好
的性能。
注意COPY会忽略规则。如果想要使用COPY插入数据，则需要拷贝到正确的子表而不是直接
放在主表中。COPY会引发触发器，因此在使用触发器方法时可以正常使用它。
规则方法的另一个缺点是，如果规则集合无法覆盖插入日期，则没有简单的方法能够强制
产生错误，数据将会无声无息地进入到主表中。
确认constraint_exclusion配置参数在postgresql.conf中没有被禁用，否则将会不必要
地访问子表。
如我们所见，一个复杂的表层次可能需要大量的DDL。在上面的例子中，我们可能为每个月
创建一个新的子表，因此编写一个脚本来自动生成所需要的DDL可能会更好。
5.11.3.2. 继承分区的维护
要快速移除旧数据，只需要简单地去掉不再需要的子表：
DROP TABLE measurement_y2006m02;
要从继承层次表中去掉子表，但还是把它当做一个表保留：
92
数据定义
ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
要增加一个新子表来处理新数据，可以像上面创建的原始子表那样创建一个空的子表：
CREATE TABLE measurement_y2008m02 (
CHECK ( logdate >= DATE ‘2008-02-01’ AND logdate < DATE ‘2008-03-01’ )
) INHERITS (measurement);
或者，用户可能想要创建新子表并且在将它加入到表层次之前填充它。这可以允许数据在被
父表上的查询可见之前对数据进行装载、检查以及转换。
CREATE TABLE measurement_y2008m02
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
CHECK ( logdate >= DATE ‘2008-02-01’ AND logdate < DATE ‘2008-03-01’ );
\copy measurement_y2008m02 from ‘measurement_y2008m02’
-# possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;
5.11.3.3. 提醒
下面的提醒适用于用继承实现的分区：
• 没有自动的方法啊验证所有的CHECK约束之间是否互斥。编写代码来产生子表以及创建和
修改相关对象比手写命令要更加安全。
• 索引和外键约束适用于单个表而不是其继承子级，因此它们有一些caveats 需要注意。
• 这里展示的模式假定行的键列值从不改变，或者说改变不足以让行移动到另一个分区。由
于CHECK约束的存在，尝试那样做的UPDATE将会失败。如果需要处理那种情况，可以在子
表上放置适当的更新触发器，但是那会使对结构的管理更加复杂。
• 如果使用手工的VACUUM或者ANALYZE命令，不要忘记需要在每个子表上单独运行它们。这
样的命令：
ANALYZE measurement;
将只会处理主表。
• 带有ON CONFLICT子句的INSERT语句不太可能按照预期工作，因为只有在指定的目标关系
而不是其子关系上发生唯一违背时才会采取ON CONFLICT行动。
• 将会需要触发器或者规则将行路由到想要的子表中，除非应用明确地知道分区的模式。编
写触发器可能会很复杂，并且会比声明式分区在内部执行的元组路由慢很多。
5.11.4. 分区剪枝
分区剪枝是一种提升声明式分区表性能的查询优化技术。例如：
SET enable_partition_pruning = on; -# the default
SELECT count() FROM measurement WHERE logdate >= DATE ‘2008-01-01’;
如果没有分区剪枝，上面的查询将会扫描measurement表的每一个分区。如果启用了分区剪
枝，规划器将会检查每个分区的定义并且检验该分区是否因为不包含符合查询WHERE子句的
行而无需扫描。当规划器可以证实这一点时，它会把分区从查询计划中排除（剪枝）。
93
数据定义
通过使用EXPLAIN命令和enable_partition_pruning配置参数，可以展示剪枝掉分区的计划
与没有剪枝的计划之间的差别。对这种类型的表设置，一种典型的未优化计划是：
SET enable_partition_pruning = off;
EXPLAIN SELECT count() FROM measurement WHERE logdate >= DATE ‘2008-01-01’;
QUERY PLAN
-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-
#-#-
Aggregate (cost=188.76…188.77 rows=1 width=8)
-> Append (cost=0.00…181.05 rows=3085 width=0)
-> Seq Scan on measurement_y2006m02 (cost=0.00…33.12 rows=617
width=0)
Filter: (logdate >= ‘2008-01-01’::date)
-> Seq Scan on measurement_y2006m03 (cost=0.00…33.12 rows=617
width=0)
Filter: (logdate >= ‘2008-01-01’::date)
…
-> Seq Scan on measurement_y2007m11 (cost=0.00…33.12 rows=617
width=0)
Filter: (logdate >= ‘2008-01-01’::date)
-> Seq Scan on measurement_y2007m12 (cost=0.00…33.12 rows=617
width=0)
Filter: (logdate >= ‘2008-01-01’::date)
-> Seq Scan on measurement_y2008m01 (cost=0.00…33.12 rows=617
width=0)
Filter: (logdate >= ‘2008-01-01’::date)
某些或者全部的分区可能会使用索引扫描取代全表顺序扫描，但是这里的重点是根本不需要
扫描较老的分区来回答这个查询。当我们启用分区剪枝时，我们会得到一个便宜很多的计
划，而它能给出相同的答案：
SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE ‘2008-01-01’;
QUERY PLAN
-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-#-
#-#-
Aggregate (cost=37.75…37.76 rows=1 width=8)
-> Seq Scan on measurement_y2008m01 (cost=0.00…33.12 rows=617 width=0)
Filter: (logdate >= ‘2008-01-01’::date)
注意，分区剪枝仅由分区键隐式定义的约束所驱动，而不是由索引的存在驱动。因此，没有
必要在键列上定义索引。是否需要为一个给定分区创建索引取决于预期的查询扫描该分区时
会扫描大部分还是小部分。后一种情况下索引的帮助会比前者大。
不仅在给定查询的规划期间可以执行分区剪枝，在其执行期间也能执行分区剪枝。这非常
有用，因为如果子句中包含查询规划时值未知的表达式时，这可以剪枝掉更多的分区；例
如在PREPARE语句中定义的参数会使用从子查询拿到的值，或者嵌套循环连接内侧关系上的
参数化值。执行期间的分区剪枝可能在下列任何时刻执行：
• 在查询计划的初始化期间。对于执行的初始化阶段就已知值的参数，可以在这里执行分区
剪枝。这个阶段中被剪枝掉的分区将不会显示在查询的EXPLAIN或EXPLAIN ANALYZE结果
中。通过观察EXPLAIN输出的“Subplans Removed”属性，可以确定被剪枝掉的分区数。
• 在查询计划的实际执行期间。这里可以使用只有在实际查询执行时才能知道的值执行分区
剪枝。这包括来自子查询的值以及来自执行时参数的值（例如来自于参数化嵌套循环连接
的参数）。由于在查询执行期间这些参数的值可能会改变多次，所以只要分区剪枝使用到
的执行参数发生改变，就会执行一次分区剪枝。要判断分区是否在这个阶段被剪枝，需要
94
数据定义
仔细地观察EXPLAIN ANALYZE输出中的loops属性。对应于不同分区的子计划可以具有不同
的值，这取决于在执行期间每个分区被修剪的次数。如果每次都被剪枝，有些分区可能
会显示为(never executed)。
可以使用enable_partition_pruning设置禁用分区剪枝。
注意
执行时间分区裁剪当前只针对Append和MergeAppend节点类型。它还没有
为ModifyTable节点类型实现，但有可能会在将来发布的 PostgreSQL中更改。
5.11.5. 分区和约束排除
约束排除是一种与分区剪枝类似的查询优化技术。虽然它主要被用于使用传统继承方法实现
的分区上，但它也可以被用于其他目的，包括用于声明式分区。
约束排除以非常类似于分区剪枝的方式工作，不过它使用每个表的CHECK约束 — 这也是它得
名的原因 — 而分区剪枝使用表的分区边界，分区边界仅存在于声明式分区的情况中。另一
点不同之处是约束排除仅在规划时应用，在执行时不会尝试移除分区。
由于约束排除使用CHECK约束，这导致它比分区剪枝要慢，但有时候可以被当作一种优点加
以利用：因为甚至可以在声明式分区的表上（在分区边界之外）定义约束，约束排除可能可
以从查询计划中消去额外的分区。
constraint_exclusion的默认（也是推荐的）设置不是on也不是off，而是一种被称
为partition的中间设置，这会导致该技术仅被应用于可能工作在继承分区表上的查
询。on设置导致规划器检查所有查询中的CHECK约束，甚至是那些不太可能受益的简单查
询。
下列提醒适用于约束排除：
• 约束排除仅适用于查询规划期间，和分区裁剪不同，在查询执行期间也可以应用。
• 只有查询的WHERE子句包含常量（或者外部提供的参数）时，约束排除才能有效果。例
如，针对一个非不变函数（如CURRENT_TIMESTAMP）的比较不能被优化，因为规划器不知
道该函数的值在运行时会落到哪个子表中。
• 保持分区约束简单化，否则规划器可能无法验证哪些子表可能不需要被访问。如前面的例
子所示，对列表分区使用简单的等值条件，对范围分区使用简单的范围测试。一种好的经
验规则是分区约束应该仅包含分区列与常量使用B-树的可索引操作符的比较，因为只有B-
树的可索引列才允许出现在分区键中。
• 约束排除期间会检查父表的所有子表上的所有约束，因此大量的子表很可能明显地增加查
询规划时间。因此，传统的基于继承的分区可以很好地处理上百个子表，不要尝试使用上
千个子表。
5.11.6. 声明分区最佳实践
应该谨慎地选择如何划分表，因为查询规划和执行的性能可能会受到不良设计的负面影响。
最重要的设计决策之一是列或者如和对数据进行分区的。通常最佳选择是按列或列集合进
行分区，这些列最常出现在分区表上执行的查询的 WHERE子句中。 WHERE子句项与分区键
匹配并兼容，可用于裁剪不需要的分区。但是，你可能会被迫根据PRIMARY KEY或UNIQUE约
束的要求做出其他决策。在规划分区策略时，删除不需要的数据也是需要考虑的一个因
素。可以相当快地分离整个分区，因此采用这样方式设计分区策略可能是有益的，既把一
次删除的所有数据都放在单个分区中。
95
数据定义
选择表应该划分的分区的目标数量也是一个重要的决策。没有足够的分区可能意味着
索引仍然太大，数据位置仍然较差，这可能导致缓存命中率很低。但是，将表划分为
太多的分区也会导致问题。在查询规划和执行期间，过多的分区可能意味着查询计划
时间较长，内存消耗也更高。在选择如何划分表时，考虑将来可能发生的更改也很重
要。例如，如果您选择为每个客户提供一个分区，而您目前只有少量的大客户，那么，如
果几年后您发现自己有大量的小客户，那么就要考虑这种影响。在这种情况下，最好选择
按HASH分区并且选择合理数量的分区，而不是尝试按 LIST 进行分区，并希望客户数量的增
长不会超出按数据分区的实际范围。
子分区可用于进一步划分预期会比其他分区更大的分区，尽管过多的子分区很容易导致大量
分区，并可能导致前一段中提到的相同问题。
考虑查询计划和执行期间的分区开销也很重要。查询规划器通常能够很好地处理多达几千
个分区的分区层次结构，前提是典型的查询允许查询规划器裁剪除了少量分区之外的所有分
区。规划器执行分区修剪后保留更多分区时，规划时间会变长，内存消耗会更高。对
于UPDATE 和 DELETE命令尤其如此。担心拥有大量分区的另一个原因是，服务器的内存消耗
可能会在一段时间内显著增加，特别是如果许多会话接触大

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/很楠不爱3/article/detail/406072

PostgreSQL 12.2 手册_postgre 手册

PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 row) mydb=> SELECT current_date; date

2016-01-07 (1 row) mydb=> SELECT 2 + 2; ?column?

{1,2,7} (1 row) 默认情况下，数组元素类型是成员表达式的公共类型，使用和UNION或CASE结构 （见第 10.5 节）相同的规则决定。你可以通过显式将数组构造器造型为想要的类型来重 载，例如： SELECT ARRAY[1,2,22.7]::integer[]; array

{{1,2},{3,4}} (1 row) SELECT ARRAY[[1,2],[3,4]]; array

{{{1,2},{3,4}},{{5,6},{7,8}},{{9,10},{11,12}}} (1 row) 你可以构造一个空数组，但是因为无法得到一个无类型的数组，你必须显式地把你的空数组 造型成想要的类型。例如： SELECT ARRAY[]::integer[]; array

{} (1 row) 也可以从一个子查询的结果构建一个数组。在这种形式中，数组构造器被写为关键 词ARRAY后跟着一个加了圆括号（不是方括号）的子查询。例如： SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE ‘bytea%’); array

{2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31,2412,2413} (1 row) SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i)); array

1 (1 row) SELECT getf1(CAST(ROW(11,‘this is a test’,2.5) AS myrowtype)); getf1

HELLO WORLD (1 row) 所有参数被按照顺序指定。结果是大写形式，因为uppercase被指定为true。另一个例子： SELECT concat_lower_or_upper(‘Hello’, ‘World’); 52 SQL语法 concat_lower_or_upper

hello world (1 row) 再次，参数uppercase被忽略，因此它被隐式地设置为false。使用命名记号法的一个优点是 参数可以用任何顺序指定，例如： SELECT concat_lower_or_upper(a => ‘Hello’, b => ‘World’, uppercase => true); concat_lower_or_upper

HELLO WORLD (1 row) SELECT concat_lower_or_upper(a => ‘Hello’, uppercase => true, b => ‘World’); concat_lower_or_upper

HELLO WORLD (1 row) 为了向后兼容性，基于 “:=” 的旧语法仍被支持： SELECT concat_lower_or_upper(a := ‘Hello’, uppercase := true, b := ‘World’); concat_lower_or_upper

HELLO WORLD (1 row) 4.3.3. 使用混合记号 混合记号法组合了位置和命名记号法。不过，正如已经提到过的，命名参数不能超越位置参 数。例如： SELECT concat_lower_or_upper(‘Hello’, ‘World’, uppercase => true); concat_lower_or_upper