PostgreSQL-9.6.0中文手册：彭煜玮译

需积分: 5 75 浏览量更新于2024-07-19 收藏 34.4MB PDF 举报

"PostgreSQL-9.6.0-CN" 是由全球知名的PostgreSQL开发组编写的官方中文手册，译者彭煜玮在其个人网站 <http://www.pengyuwei.net> 上分享。PostgreSQL是一款强大的开源对象关系型数据库管理系统，版本9.6.0在文档中提供了详尽的功能介绍和技术细节。该手册的核心内容涵盖了PostgreSQL 9.6.0的所有关键特性，包括但不限于数据存储、查询语言（SQL）优化、事务处理、并发控制、存储过程、触发器、索引以及备份恢复等。它旨在帮助用户理解和掌握如何有效地管理和操作这个数据库系统，无论是开发人员、数据库管理员还是系统架构师都能从中受益。版权方面，PostgreSQL和Postgres95都受到1996年至2016年PostgreSQL全球开发组的版权保护。用户可以在不支付费用的情况下，根据许可证条款自由使用、复制、修改和分发软件及其文档，但必须确保在所有副本中保留版权声明和相关许可条款。此外，手册中包含了关于责任免除的法律通知，强调即使在使用过程中发生损失或损害，加州大学不会承担任何直接或间接责任，除非是故意或过失造成的。在阅读这份中文手册时，用户不仅能得到技术指导，还能了解到PostgreSQL团队对于用户隐私和数据安全的重视，以及对于潜在风险的警示。对于学习者来说，这是一份宝贵的参考资料，对于专业人员则是日常工作中不可或缺的工具。通过深入研究这份文档，用户可以提升自己的数据库管理技能，并了解PostgreSQL如何在现代IT环境中发挥核心作用。

31.1. 运行测试 ..............................................................................................................640

31.1.1. 在一个临时安装上运行测试..................................................................640

31.1.2. 在一个现有安装上运行测试..................................................................640

31.1.3. 附加测试套件..........................................................................................641

31.1.4. 区域和编码..............................................................................................641

31.1.5. 额外测试..................................................................................................642

31.1.6. 测试热备..................................................................................................642

31.2. 测试评估 ..............................................................................................................643

31.2.1. 错误消息差异..........................................................................................643

31.2.2. 区域差异..................................................................................................643

31.2.3. 日期和时间差异......................................................................................644

31.2.4. 浮点差异..................................................................................................644

31.2.5. 行序差异..................................................................................................644

31.2.6. 栈深度不足..............................................................................................644

31.2.7. “失败”测试.............................................................................................. 645

31.2.8. 配置参数..................................................................................................645

31.3. 变体比较文件 ......................................................................................................645

31.4. TAP Tests..............................................................................................................646

31.5. 测试覆盖检查 ......................................................................................................646

IV. 客客客户户户端端端接接接口口口 ............................................................................................................................... 647

32. libpq - C 库 .......................................................................................................................649

32.1. 数据库连接控制函数 ..........................................................................................649

32.1.1. 连接字符串..............................................................................................654

32.1.1.1. 关键词/值连接字符串................................................................655

32.1.1.2. 连接 URI .....................................................................................655

32.1.2. 参数关键词..............................................................................................656

32.2. 连接状态函数 ......................................................................................................659

32.3. 命令执行函数 ......................................................................................................663

32.3.1. 主要函数..................................................................................................664

32.3.2. 检索查询结果信息..................................................................................670

32.3.3. 检索其他结果信息..................................................................................674

32.3.4. 用于包含在 SQL 命令中的转移串 ........................................................674

32.4. 异步命令处理 ......................................................................................................677

32.5. 一行一行地检索查询结果 ..................................................................................680

32.6. 取消进行中的查询 ..............................................................................................681

32.7. 快速路径接口 ......................................................................................................682

32.8. 异步提示 ..............................................................................................................683

32.9. COPY命令相关的函数 .........................................................................................684

32.9.1. 用于发送COPY数据的函数.....................................................................684

32.9.2. 用于接收COPY数据的函数.....................................................................685

32.9.3. 用于COPY的废弃函数.............................................................................686

32.10. 控制函数 ............................................................................................................687

32.11. 杂项函数 ............................................................................................................689

32.12. 通知处理 ............................................................................................................691

32.13. 事件系统 ............................................................................................................692

32.13.1. 事件类型................................................................................................692

32.13.2. 事件回调函数........................................................................................694

32.13.3. 事件支持函数........................................................................................694

32.13.4. 事件实例................................................................................................695

32.14. 环境变量 ............................................................................................................698

32.15. 口令文件 ............................................................................................................699

xvi

32.16. 连接服务文件 ....................................................................................................699

32.17. 连接参数的 LDAP 查找....................................................................................700

32.18. SSL 支持 ............................................................................................................701

32.18.1. 服务器证书的客户端验证....................................................................701

32.18.2. 客户端证书............................................................................................701

32.18.3. 不同模式中提供的保护........................................................................702

32.18.4. SSL 客户端文件使用............................................................................703

32.18.5. SSL 库初始化........................................................................................704

32.19. 在线程化程序中的行为 ....................................................................................704

32.20. 编译 libpq 程序.................................................................................................. 705

32.21. 例子程序 ............................................................................................................706

33. 大对象............................................................................................................................... 715

33.1. 简介 ......................................................................................................................715

33.2. 实现特性 ..............................................................................................................715

33.3. 客户端接口 ..........................................................................................................715

33.3.1. 创建一个大对象......................................................................................715

33.3.2. 导入一个大对象......................................................................................716

33.3.3. 导出一个大对象......................................................................................716

33.3.4. 打开一个现有的大对象..........................................................................717

33.3.5. 向一个大对象写入数据..........................................................................717

33.3.6. 从一个大对象读取数据..........................................................................717

33.3.7. 在一个大对象中查找..............................................................................718

33.3.8. 获取一个大对象的查找位置..................................................................718

33.3.9. 截断一个大对象......................................................................................718

33.3.10. 关闭一个大对象描述符........................................................................719

33.3.11. 移除一个大对象....................................................................................719

33.4. 服务器端函数 ......................................................................................................719

33.5. 例子程序 ..............................................................................................................720

34. ECPG - C 中的嵌入式 SQL.............................................................................................726

34.1. 概念 ......................................................................................................................726

34.2. 管理数据库连接 ..................................................................................................726

34.2.1. 连接到数据库服务器..............................................................................726

34.2.2. 选择一个连接..........................................................................................727

34.2.3. 关闭一个连接..........................................................................................728

34.3. 运行 SQL 命令..................................................................................................... 729

34.3.1. 执行 SQL 语句 ........................................................................................729

34.3.2. 使用游标..................................................................................................730

34.3.3. 管理事务..................................................................................................730

34.3.4. 预备语句..................................................................................................731

34.4. 使用主变量 ..........................................................................................................732

34.4.1. 概述..........................................................................................................732

34.4.2. 声明小节..................................................................................................732

34.4.3. 检索查询结果..........................................................................................733

34.4.4. 类型映射..................................................................................................733

34.4.4.1. 处理字符串.................................................................................734

34.4.4.2. 访问特殊数据类型.....................................................................735

34.4.4.2.1. timestamp, date............................................................... 735

34.4.4.2.2. interval ............................................................................736

34.4.4.2.3. numeric, decimal.............................................................736

34.4.4.3. 非简单类型的主变量.................................................................737

34.4.4.3.1. 数组 ................................................................................737

34.4.4.3.2. 结构 ................................................................................738

xvii

34.4.4.3.3. Typedefs.......................................................................... 740

34.4.4.3.4. 指针 ................................................................................740

34.4.5. 处理非简单 SQL 数据类型 ....................................................................740

34.4.5.1. 数组.............................................................................................740

34.4.5.2. 组合类型.....................................................................................742

34.4.5.3. 用户定义的基础类型.................................................................743

34.4.6. 指示符......................................................................................................745

34.5. 动态 SQL.............................................................................................................. 745

34.5.1. 执行没有结果集的语句..........................................................................745

34.5.2. 执行一个有输入参数的语句..................................................................746

34.5.3. 执行一个有结果集的语句......................................................................746

34.6. pgtypes 库.............................................................................................................747

34.6.1. numeric类型 ............................................................................................ 747

34.6.2. 日期类型..................................................................................................750

34.6.3. 时间戳类型..............................................................................................753

34.6.4. 区间类型..................................................................................................756

34.6.5. decimal类型............................................................................................. 757

34.6.6. pgtypeslib 的 errno 值 .............................................................................757

34.6.7. pgtypeslib 的特殊常量............................................................................ 758

34.7. 使用描述符区域 ..................................................................................................759

34.7.1. 命名 SQL 描述符区域 ............................................................................759

34.7.2. SQLDA 描述符区域 ...............................................................................761

34.7.2.1. SQLDA 数据结构....................................................................... 761

34.7.2.1.1. sqlda_t 结构.................................................................... 762

34.7.2.1.2. sqlvar_t 结构...................................................................762

34.7.2.1.3. struct sqlname 结构 ........................................................763

34.7.2.2. 使用一个 SQLDA 检索一个结果集..........................................764

34.7.2.3. 使用一个 SQLDA 传递查询参数..............................................765

34.7.2.4. 一个使用 SQLDA 的应用例子..................................................766

34.8. 错误处理 ..............................................................................................................771

34.8.1. 设置回调..................................................................................................772

34.8.2. sqlca ......................................................................................................... 773

34.8.3. SQLSTATE 与 SQLCODE ...........................................................................775

34.9. 预处理器指令 ......................................................................................................778

34.9.1. 包括文件..................................................................................................778

34.9.2. deﬁne 和 undef 指令................................................................................778

34.9.3. ifdef、ifndef、else、elif 以及 endif 指令..............................................779

34.10. 处理嵌入式 SQL 程序....................................................................................... 780

34.11. 库函数 ................................................................................................................781

34.12. 大对象 ................................................................................................................781

34.13. C++ 应用............................................................................................................ 783

34.13.1. 主变量的可见范围................................................................................783

34.13.2. 使用外部 C 模块的 C++ 应用开发...................................................... 784

34.14. 嵌入式 SQL 命令............................................................................................... 786

ALLOCATE DESCRIPTOR ...............................................................................786

CONNECT...........................................................................................................788

DEALLOCATE DESCRIPTOR .......................................................................... 791

DECLARE ...........................................................................................................792

DESCRIBE ..........................................................................................................794

DISCONNECT ....................................................................................................795

EXECUTE IMMEDIATE.................................................................................... 797

GET DESCRIPTOR ............................................................................................ 798

xviii

OPEN ...................................................................................................................801

PREPARE ............................................................................................................802

SET AUTOCOMMIT .......................................................................................... 803

SET CONNECTION ........................................................................................... 804

SET DESCRIPTOR............................................................................................. 805

TYPE....................................................................................................................807

VAR...................................................................................................................... 809

WHENEVER .......................................................................................................810

34.15. Informix兼容模式..............................................................................................812

34.15.1. 附加类型................................................................................................812

34.15.2. 附加的/缺少的嵌入式 SQL 语句........................................................812

34.15.3. Informix-兼容的 SQLDA 描述符区域................................................. 813

34.15.4. 附加函数................................................................................................816

34.15.5. 额外的常量............................................................................................823

34.16. 内部 ....................................................................................................................824

35. 信息模式........................................................................................................................... 827

35.1. 模式 ......................................................................................................................827

35.2. 数据类型 ..............................................................................................................827

35.3. information_schema_catalog_name ........................................................... 828

35.4. administrable_role_authorizations....................................................... 828

35.5. applicable_roles............................................................................................828

35.6. attributes.........................................................................................................829

35.7. character_sets................................................................................................832

35.8. check_constraint_routine_usage .............................................................833

35.9. check_constraints .........................................................................................833

35.10. collations.......................................................................................................834

35.11. collation_character_set_applicability ............................................834

35.12. column_domain_usage ...................................................................................834

35.13. column_options..............................................................................................835

35.14. column_privileges .......................................................................................835

35.15. column_udt_usage..........................................................................................836

35.16. columns .............................................................................................................836

35.17. constraint_column_usage .......................................................................... 840

35.18. constraint_table_usage............................................................................. 841

35.19. data_type_privileges.................................................................................841

35.20. domain_constraints .....................................................................................842

35.21. domain_udt_usage..........................................................................................842

35.22. domains .............................................................................................................843

35.23. element_types ................................................................................................845

35.24. enabled_roles ................................................................................................847

35.25. foreign_data_wrapper_options................................................................848

35.26. foreign_data_wrappers............................................................................... 848

35.27. foreign_server_options............................................................................. 849

35.28. foreign_servers............................................................................................ 849

35.29. foreign_table_options............................................................................... 849

35.30. foreign_tables..............................................................................................850

35.31. key_column_usage..........................................................................................850

35.32. parameters.......................................................................................................851

35.33. referential_constraints .......................................................................... 853

35.34. role_column_grants .....................................................................................854

35.35. role_routine_grants ...................................................................................854

35.36. role_table_grants .......................................................................................855

xix

35.37. role_udt_grants............................................................................................ 856

35.38. role_usage_grants .......................................................................................856

35.39. routine_privileges .....................................................................................857

35.40. routines........................................................................................................... 857

35.41. schemata........................................................................................................... 862

35.42. sequences.........................................................................................................863

35.43. sql_features ..................................................................................................864

35.44. sql_implementation_info .......................................................................... 864

35.45. sql_languages ................................................................................................865

35.46. sql_packages ..................................................................................................865

35.47. sql_parts.........................................................................................................866

35.48. sql_sizing.......................................................................................................866

35.49. sql_sizing_profiles ...................................................................................867

35.50. table_constraints .......................................................................................867

35.51. table_privileges..........................................................................................868

35.52. tables ............................................................................................................... 868

35.53. transforms.......................................................................................................869

35.54. triggered_update_columns ........................................................................ 870

35.55. triggers........................................................................................................... 870

35.56. udt_privileges..............................................................................................872

35.57. usage_privileges..........................................................................................872

35.58. user_defined_types .....................................................................................873

35.59. user_mapping_options.................................................................................874

35.60. user_mappings ................................................................................................875

35.61. view_column_usage .......................................................................................875

35.62. view_routine_usage .....................................................................................876

35.63. view_table_usage..........................................................................................876

35.64. views .................................................................................................................877

V. 服服服务务务器器器编编编程程程................................................................................................................................. 879

36. 扩展 SQL ..........................................................................................................................881

36.1. 扩展性如何工作 ..................................................................................................881

36.2. PostgreSQL类型系统...........................................................................................881

36.2.1. 基础类型..................................................................................................881

36.2.2. 组合类型..................................................................................................881

36.2.3. 域..............................................................................................................882

36.2.4. 伪类型......................................................................................................882

36.2.5. 多态类型..................................................................................................882

36.3. 用户定义的函数 ..................................................................................................883

36.4. 查询语言（SQL）函数.......................................................................................883

36.4.1. SQL函数的参数...................................................................................... 884

36.4.2. 基本类型上的SQL.................................................................................. 884

36.4.3. 组合类型上的SQL函数.......................................................................... 886

36.4.4. 带有输出参数的SQL函数...................................................................... 889

36.4.5. 带有可变数量参数的SQL函数.............................................................. 890

36.4.6. 带有参数默认值的SQL函数.................................................................. 891

36.4.7. SQL 函数作为表来源............................................................................. 891

36.4.8. 返回集合的SQL函数.............................................................................. 892

36.4.9. 返回TABLE的SQL函数 ........................................................................... 894

36.4.10. 多态SQL函数........................................................................................ 894

36.4.11. 带有排序规则的SQL函数.................................................................... 896

36.5. 函数重载 ..............................................................................................................897

剩余3127页未读，继续阅读

心若醉

粉丝: 0
资源: 3

PostgreSQL-9.6.0中文手册：彭煜玮译

Internals Of PostgreSQL Wal.pdf

postgresql-9.6.9.tar.gz

postgresql-9.6.3.tar.gz 下载

postgresql-phppgadmin容器和postgresql-postgres区别

windows如何将postgresql-x64-14服务修改为my_postgresql-x64-14

sudo: postgresql-setup: command not found

安装postgresql-devel

postgresql-libs-9.2.24-7.el7_9.x86_64 linux安装

liunx中重启pgsql数据库报错：Job for postgresql-12.service failed because the control process exited with error code. See "systemctl status postgresql-12.service" and "journalctl -xe" for details. 如何解决

最新资源