PostgreSQL - tutorial

本文翻译整理自：官方文档 Preface 和 第一部分（I. Tutorial）
有需要的可以前往官方文档查看：https://www.postgresql.org/docs/15/index.html

---

---

序言

本书是PostgreSQL的官方留档，由PostgreSQL开发人员和其他志愿者与PostgreSQL软件开发并行编写，描述了当前版本PostgreSQL官方支持的所有功能。

为了使有关PostgreSQL的大量信息易于管理，本书分为几个部分。
每个部分都针对不同类别的用户，或处于PostgreSQL体验不同阶段的用户：

• 第一部分是对新用户的非正式介绍。
• 第二部分记录了SQL查询语言环境，包括数据类型和函数，以及用户级性能调整。
  每个PostgreSQL用户都应该阅读这篇文章
• 第三部分描述了服务器的安装和管理。
  每个运行PostgreSQL服务器的人，无论是私人使用还是他人使用，都应该阅读这一部分。
• 第四部分描述了PostgreSQL客户端程序的编程接口。
• 第五部分为高级用户提供有关服务器可扩展性功能的信息。
  主题包括用户定义的数据类型和功能。
• 第六部分包含有关SQL命令、客户端和服务器程序的参考信息。
  这部分支持按命令或程序排序的结构化信息的其他部分。
• 第七部分包含可能对PostgreSQL开发人员有用的各种信息。

---

1.什么是PostgreSQL？

PostgreSQL是一个基于POSTGRES，版本4.2的对象关系数据库管理系统（ORDBMS），由加州大学伯克利分校计算机科学系开发。
POSTGRES开创了许多概念，直到很久以后才在一些商业数据库系统中可用。

PostgreSQL是这个原始Berkeley代码的开源后代。
它支持大部分SQL标准，并提供许多现代功能：

• 复杂查询
• 外键
• 触发器
• 可更新视图
• 事务完整性
• 多版本并发控制

---

此外，用户可以通过多种方式扩展PostgreSQL，例如通过添加新的

• 数据类型
• 函数
• 算子
• 聚合函数
• 索引方法
• 程序语言

---

由于自由许可，任何人都可以出于任何目的免费使用、修改和分发PostgreSQL，无论是私人的、商业的还是学术的。

---

2. PostgreSQL简史

现在被称为PostgreSQL的 对象关系数据库管理系统 源自 加州大学伯克利分校编写的POSTGRES包。
经过几十年的发展，PostgreSQL现在是世界上最先进的开源数据库。

---

2.1 伯克利POSTGRES项目

由Michael Stonebraker教授领导的POSTGRES项目 由国防高级研究计划局（DARPA）、陆军研究办公室（ARO）、美国国家科学基金会（NSF）和 ESL, Inc.赞助。
POSTGRES的实施始于1986年。
系统的初始概念在[ston86]中提出，初始数据模型的定义出现在[rowe87]。
当时规则系统的设计在[ston87a]中进行了描述。
存储管理器的基本原理和架构在[ston87b].

POSTGRES从那时起经历了几个主要版本。
第一个demoware 系统于1987年开始运行，并在1988年的ACM-SIGMOD会议上展示。
版本1，描述于[ston90a]，于1989年6月发布给少数外部用户。
为了回应对第一个规则系统的批评（[ston89]），规则系统被重新设计（[ston90b]），版本2于1990年6月发布了新的规则系统。
版本3出现在1991年，增加了对多个存储管理器的支持、改进的查询执行器和重写的规则系统。
在大多数情况下，后续版本直到Postgres95（见下文）专注于可移植性和可靠性。

POSTGRES已被用于实现许多不同的研究和生产应用程序。
其中包括：金融数据分析系统、喷气发动机性能监控包、小行星跟踪数据库、医疗信息数据库和几个地理信息系统。
POSTGRES还被用作几所大学的教育工具。
最后，Illustra Information Technologies（后来并入Informix，现在归IBM所有）拿起代码并将其商业化。
1992年底，POSTGRES成为红杉2000科学计算项目的主要数据管理员

1993年，外部用户群体的规模几乎翻了一番。
越来越明显的是，原型代码和支持的维护占用了本应用于数据库研究的大量时间。
为了减轻这种支持负担，伯克利POSTGRES项目以4.2版正式结束。

---

2.2. Postgres95

1994年，Andrew Yu和Jory Chen为POSTGRES添加了SQL语言解释器。
Postgres95随后以新名称发布到网络上，作为原始POSTGRES Berkeley代码的开源后代在世界上寻找自己的方式。

Postgres95 代码完全采用ANSI C语言，并将大小缩减了25%。许多内部更改提高了性能和可运维性。
Postgres95 1.0. x版在威斯康星基准测试中的运行速度比POSTGRES 4.2版快30-50%。除了bug修复之外，以下是主要增强：

• 查询语言PostQUEL被替换为SQL（在服务器中实现）。
  （接口库libpq以PostQUEL命名。）直到PostgreSQL才支持子查询（见下文），但它们可以在Postgres95中使用用户定义的SQL函数进行模仿。
  聚合函数被重新实现。
  还添加了对GROUP BY查询子句的支持。
• 为交互式SQL查询提供了一个新程序（psql），它使用GNU Readline。
  这在很大程度上取代了旧的监控程序。
• 一个新的前端库libpgtcl支持基于Tcl的客户端。
  一个示例shellpgtclsh提供了新的Tcl命令来连接Tcl程序和Postgres95服务器。
• 大修了大对象接口。
  反转大对象是存储大对象的唯一机制。
  （反转文件系统已删除。）
• 实例级规则系统已删除。
  规则仍然可以作为重写规则使用。
• 随源代码分发了介绍常规SQL功能以及Postgres95功能的简短教程
• 构建使用GNU make（而不是BSD make）。
  此外，Postgres95可以使用未打补丁的GCC进行编译（双精度数据对齐是固定的）。

---

2.3. PostgreSQL

到1996年，很明显 Postgres95 这个名字经不起时间的考验。
我们选择了一个新名字，PostgreSQL，以反映原始POSTGRES和具有SQL能力的最新版本之间的关系。
同时，我们将版本编号设置为从6.0开始，将数字放回最初由伯克利POSTGRES项目开始的序列中。

许多人继续将PostgreSQL称为 Postgres （现在很少用大写字母），因为传统或因为它更容易发音。这种用法被广泛接受为昵称或别名。

Postgres95开发过程中的重点是识别和理解服务器代码中存在的问题。
对于PostgreSQL，重点已经转移到增强特性和功能上，尽管所有领域的工作都在继续。

从那以后在PostgreSQL中发生的事情的详细信息可以在附录E中找到。

---

3.公约

命令提要中使用以下约定：括号（[和]）表示可选部分。
大括号（{和}）和垂直线（|）表示必须选择一个替代方案。
点（...）表示可以重复前面的元素。
所有其他符号，包括括号，都应该按字面意思理解。

为了提高清晰度，SQL命令前面有提示符=>，shell命令前面有提示符$。
不过，通常不会显示提示符。

管理员通常是负责安装和运行服务器的人。
用户可以是正在使用或想要使用PostgreSQL系统任何部分的任何人。
这些术语不应解释得太狭隘；本书对系统管理过程没有固定的假设。

---

4.进一步信息

除了留档，也就是本书，还有其他关于PostgreSQL的资源：

• 维基
  PostgreSQL wiki包含项目的FAQ（常见问题）列表、TODO列表以及更多主题的详细信息。
• 网址
  PostgreSQL 网站提供最新版本的详细信息和其他信息，使您的工作或玩PostgreSQL更有效率。
• 邮件列表
  邮件列表是回答您的问题、与其他用户分享经验以及联系开发人员的好地方。
  有关详细信息，请参阅PostgreSQL网站。
• 你自己！
  PostgreSQL是一个开源项目。因此，它依赖于用户社区的持续支持。
  当你开始使用PostgreSQL时，你将依靠其他人的帮助，无论是通过留档还是通过邮件列表。
  考虑贡献你的知识。
  阅读邮件列表并回答问题。
  如果你学到了一些不在留档中的东西，把它写下来并贡献出来。
  如果你在代码中添加了功能，贡献出来。

---

5.错误报告指南

当您在PostgreSQL中发现bug时，我们想听听它。
您的bug报告在使PostgreSQL更加可靠方面发挥着重要作用，因为即使非常小心也不能保证PostgreSQL的每个部分在任何情况下都能在每个平台上工作。

以下建议旨在帮助您形成可以有效处理的bug报告。
没有人需要遵循它们，但这样做往往对每个人都有好处。

我们不能承诺立即修复每个bug。
如果bug是明显的、关键的，或者影响了很多用户，很有可能有人会调查它。
也可能发生我们告诉您更新到更新版本，看看bug是否发生在那里。
或者我们可能会决定在我们可能计划的一些重大重写完成之前无法修复bug。
或者这太难了，议程上有更重要的事情。
如果您立即需要帮助，请考虑获得商业支持合同。

---

5.1 识别错误

在您报告bug之前，请反复阅读留档，以验证您是否真的可以做任何您正在尝试的事情。
如果从留档中不清楚您是否可以做某事，也请报告；这是留档中的bug。
如果结果证明一个程序做了与留档所说的不同的事情，那就是bug。
这可能包括但不限于以下情况：

• 程序以致命信号或操作系统误差消息终止，这将指向程序中的问题。
  （反例可能是“磁盘已满”消息，因为您必须自己修复它。）
• 程序为任何给定的输入生成错误的输出。
• 程序拒绝接受有效的输入（在留档中定义）。
• 程序在没有通知或错误消息的情况下接受无效输入。
  但是请记住，您对无效输入的想法可能是我们对传统实践的扩展或兼容性的想法。
• PostgreSQL无法根据支持平台上的说明进行编译、构建或安装。

这里的“程序”是指任何可执行文件，而不仅仅是后端进程。

缓慢或占用资源不一定是bug。
阅读留档或在邮件列表中寻求帮助来调整您的应用程序。
不符合SQL标准也不一定是bug，除非明确声明符合特定功能。

在继续之前，请检查TODO列表和常见问题解答，看看您的bug是否已经知道。
如果您无法解码TODO列表中的信息，请报告您的问题。
我们至少可以让TODO列表更清晰。

---

5.2 报告什么

关于bug报告，最重要的是要记住陈述所有事实，并且只陈述事实。
不要猜测你认为哪里出了问题，“它似乎做了什么”，或者程序的哪个部分有问题。
如果你不熟悉实现，你可能会猜错，对我们一点帮助都没有。
即使你是，有根据的解释是事实的一个很好的补充，但不能替代事实。
如果我们要解决bug我们仍然必须先看到它发生在我们自己身上。
报告赤裸裸的事实相对简单（你可能可以从屏幕上复制和粘贴它们），但很多时候重要的细节被遗漏了，因为有人认为没关系，或者报告无论如何都会被理解。

每份bug报告应载有下列项目：

• 重现问题所需的从程序启动开始的步骤的确切顺序。
  这应该是独立的；如果输出应该依赖于表中的数据，那么在没有前面的CREATE TABLE和INSERT语句的情况下发送裸SELECT语句是不够的。
  我们没有时间对您的数据库模式进行逆向工程，如果我们应该编造自己的数据，我们可能会错过问题。
  SQL相关问题的测试用例的最佳格式是可以通过显示问题的psql前端运行的文件。
  （确保您的~/.psqlrc启动文件中没有任何内容。）创建此文件的一种简单方法是使用pg_dump转储设置场景所需的表声明和数据，然后添加问题查询。
  我们鼓励您最小化示例的大小，但这不是绝对必要的。
  如果bug是可重现的，我们会找到它的任何一种方式。
  如果您的应用程序使用其他客户端接口，例如PHP，那么请尝试隔离有问题的查询。
  我们可能不会设置Web服务器来重现您的问题。
  无论如何，请记住提供确切的输入文件；不要猜测问题发生在“大文件”或“中型数据库”等，因为这些信息太不准确而无法使用。
• 你得到的输出。
  请不要说它“不工作”或“崩溃”。
  如果有错误消息，即使你不明白，也要显示出来。
  如果程序因操作系统误差而终止，说出来。
  如果什么都没发生，就说出来。
  即使你的测试用例的结果是程序崩溃或其他明显的结果，它也可能不会在我们的平台上发生。
  如果可能的话，最简单的事情是从终端复制输出。

---

Note : 如果要报告错误消息，请获取消息的最详细形式。
在psql中，事先 say\set VERBOSITY verbose。
如果要从服务器日志中提取消息，请将运行时参数log_error_verbosity设置为verbose，以便记录所有详细信息。

---

Note : 如果发生致命错误，客户端报告的错误消息可能不包含所有可用信息。
还请查看数据库服务器的日志输出。
如果您不保留服务器的日志输出，这将是开始这样做的好时机。

---

• 你期望的输出是非常重要的状态。
  如果你只是写“这个命令给我那个输出”或“这不是我期望的”，我们可能会自己运行它，扫描输出，并认为它看起来不错，正是我们期望的。
  我们不应该花时间解码命令背后的确切语义学。
  尤其是不要仅仅说“这不是SQL说的/甲骨文做的。
  ”从SQL中挖掘出正确的行为并不是一件有趣的事情，我们也不知道所有其他关系数据库的行为。
  （如果你的问题是程序崩溃，你显然可以省略这一项。）

• 任何命令行选项和其他启动选项，包括您从默认值更改的任何相关环境变量或配置文件。
  同样，请提供确切信息。
  如果您使用的是在启动时启动数据库服务器的预打包发行版，您应该尝试了解如何做到这一点。

• 您所做的任何事情都与安装说明不同。

• PostgreSQL版本。
  您可以运行命令SELECT version();以找出您连接的服务器的版本。
  大多数可执行程序还支持--version选项；至少postgres --version和psql --version应该可以工作。
  如果功能或选项不存在，那么您的版本已经足够旧，可以升级。
  如果您运行预打包版本，例如RPM，请这样说，包括包可能具有的任何颠覆。
  如果您正在谈论Git快照，请提及这一点，包括提交哈希。

  如果您的版本高于15.8，我们几乎肯定会告诉您升级。
  每个新版本都有许多bug修复和改进，因此您在旧版本PostgreSQL中遇到的bug很可能已经修复。
  我们只能为使用旧版本PostgreSQL的站点提供有限的支持；如果您的需求超出了我们的能力，请考虑获得商业支持合同。

• 平台信息。
  这包括内核名称和版本、C库、处理器、内存信息等。
  在大多数情况下，报告供应商和版本就足够了，但不要假设每个人都知道“Debian”到底包含什么，或者每个人都在x86_64上运行。
  如果你有安装问题，那么关于你机器上的工具链（编译器、make等）的信息也是必要的。

---

如果你的bug报告变得相当冗长，不要害怕。这是生活中的一个事实。
第一次报告一切比我们不得不从你那里挤出事实要好。
另一方面，如果你的输入文件很大，首先询问是否有人有兴趣调查它是公平的。
这里有一篇文章概述了更多关于报告错误的技巧。

不要把所有的时间都花在弄清楚输入中的哪些变化会让问题消失。这可能无助于解决问题。
如果事实证明bug不能马上解决，你仍然有时间找到并分享你的解决方法。
同样，不要浪费时间猜测为什么bug存在。我们很快就会发现的。

在撰写bug报告时，请避免混淆术语。
软件包总共被称为“PostgreSQL”，有时简称为“Postgres”。
如果您专门谈论后端进程，请提及这一点，不要只是说“PostgreSQL崩溃”。
单个后端进程的崩溃与父“postgres”进程的崩溃有很大不同；当您表示单个后端进程出现故障时，请不要说“服务器崩溃”，反之亦然。
此外，交互式前端“psql”等客户端程序与后端完全分开。
请尽量具体说明问题是在客户端还是服务器端。

---

5.3.在哪里报告错误

通常，将bug报告发送到bug报告邮件列表<pgsql-bugs@lists.postgresql.org>。
要求您为电子邮件消息使用描述性主题，可能是错误消息的一部分。

另一种方法是填写项目网站上提供的bug报告Web表单。
以这种方式输入bug报告会导致它被邮寄到<pgsql-bugs@lists.postgresql.org>邮件列表。

如果您的bug报告具有安全隐患，并且您不希望它在公共档案中立即可见，请不要将其发送给pgsql-bugs。
安全问题可以私下报告给<security@postgresql.org>。

不要向任何用户邮件列表发送bug报告，例如<pgsql-sql@lists.postgresql.org>或<pgsql-general@lists.postgresql.org>。
这些邮件列表用于回答用户问题，其订阅者通常不希望收到bug报告。
更重要的是，他们不太可能修复它们。

此外，请不要将报告发送到开发人员的邮件列表<pgsql-hackers@lists.postgresql.org>。
此列表用于讨论PostgreSQL的开发，如果我们能将bug报告分开，那就太好了。
如果问题需要更多审查，我们可能会选择讨论您关于pgsql-hackers的bug报告。

如果你对留档有问题，最好举报的地方是留档邮件列表<pgsql-docs@lists.postgresql.org>，请具体说明你对留档的哪一部分不满意。

如果您的bug在不受支持的平台上存在可移植性问题，请发送邮件至<pgsql-hackers@lists.postgresql.org>，以便我们（以及您）可以将PostgreSQL移植到您的平台。

---

Note : 由于大量垃圾邮件，除非您订阅，否则上述所有列表都将被审核。
这意味着电子邮件交付之前会有一些延迟。
如果您想订阅这些列表，请访问 https://lists.postgresql.org/ 获取说明。

---

第1章 入门

欢迎来到PostgreSQL教程。以下几章旨在向那些不熟悉其中任何一个方面的人简单介绍PostgreSQL、关系数据库概念和SQL语言。

我们只假设一些关于如何使用计算机的一般知识。不需要特别的Unix或编程经验。

这部分主要是为了给你一些关于PostgreSQL系统重要方面的实践经验。它不试图对它所涵盖的主题进行完整或彻底的处理。

完成本教程后，您可能希望继续阅读第II部分，以获得更正式的SQL语言知识，或者阅读第IV部分，了解有关为PostgreSQL开发应用程序的信息。那些设置和管理自己的服务器的人也应该阅读第III部分。

---

1.1 安装

当然，在使用PostgreSQL之前，您需要安装它。
您的站点可能已经安装了PostgreSQL，因为它包含在您的操作系统发行版中，或者因为系统管理员已经安装了它。
如果是这种情况，您应该从操作系统留档或系统管理员那里获取有关如何访问PostgreSQL的信息。

如果您不确定PostgreSQL是否已经可用，或者您是否可以将其用于实验，那么您可以自己安装它。
这样做并不难，这可能是一个很好的练习。
PostgreSQL可以由任何非特权用户安装；不需要超级用户（root）访问权限。

如果您自己安装PostgreSQL，请参阅第17章的安装说明，安装完成后返回本指南。
请务必密切关注有关设置适当环境变量的部分。

如果您的站点管理员没有按照默认方式进行设置，您可能需要做更多的工作。
例如，如果数据库服务器计算机是远程计算机，您需要将PGHOST环境变量设置为数据库服务器计算机的名称。
环境变量PGPORT也可能需要设置。
底线是：如果您尝试启动一个应用程序，但它抱怨无法连接到数据库，您应该咨询您的站点管理员，或者如果是您，请咨询留档以确保您的环境设置正确。
如果您不理解前一段，请阅读下一节。

---

1.2.架构基础

在我们继续之前，您应该了解基本的PostgreSQL系统架构。
了解PostgreSQL各部分的交互方式将使本章更加清晰。

在数据库术语中，PostgreSQL使用 client/server 模型。
PostgreSQL会话由以下协作进程（程序）组成：

• 一个服务器进程，它管理数据库文件，接受客户端应用程序与数据库的连接，并代表客户端执行数据库操作。
  数据库服务器程序称为postgres。
• 想要执行数据库操作的用户的客户端（前端）应用程序。
  客户端应用程序的性质可以非常多样化：客户端可以是面向文本的工具、图形应用程序、访问数据库以显示网页的Web服务器或专门的数据库维护工具。
  一些客户端应用程序随PostgreSQL发行版一起提供；大多数由用户开发。

---

与典型的客户端/服务器应用程序一样，客户端和服务器可以位于不同的主机上。
在这种情况下，它们通过TCP/IP网络连接进行通信。
您应该记住这一点，因为可以在客户端计算机上访问的文件可能无法在数据库服务器计算机上访问（或者只能使用不同的文件名访问）。

该PostgreSQL服务器可以处理来自客户端的多个并发连接。
为了实现这一点，它为每个连接启动（“fork”）一个新进程。
从那时起，客户端和新服务器进程进行通信，而不会受到原始postgres进程的干预。
因此，主管服务器进程始终在运行，等待客户端连接，而客户端和相关的服务器进程来来去去。
（所有这些当然对用户来说都是不可见的。我们在这里提到它只是为了完整。）

---

1.3.创建数据库

查看您是否可以访问数据库服务器的第一个测试是 尝试创建一个数据库。
正在运行的PostgreSQL服务器可以管理许多数据库。
通常，每个项目或每个用户都使用一个单独的数据库。

可能，您的站点管理员已经创建了一个数据库供您使用。
在这种情况下，您可以省略此步骤并跳到下一部分。

要创建新数据库，在此名为mydb的示例中，您可以使用以下命令：

$ createdb mydb

---

如果这没有产生响应，则此步骤成功，您可以跳过本节的其余部分。

如果您看到类似于以下内容的消息：

createdb: command not found

---

然后PostgreSQL没有正确安装。
要么根本没有安装，要么您的shell的搜索路径没有设置为包含它。
尝试使用绝对路径调用命令：

$ /usr/local/pgsql/bin/createdb mydb

您站点的路径可能不同。请联系您的站点管理员或检查安装说明以更正此情况。

---

另一种回应可能是：

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

---

这意味着服务器没有启动，或者它没有监听createdb期望联系它的地方。再次，检查安装说明或咨询管理员。

另一种回应可能是：

createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "joe" does not exist

---

其中提到了您自己的登录名。
如果管理员没有为您创建PostgreSQL用户帐户，则会发生这种情况。
（PostgreSQL用户帐户不同于操作系统用户帐户。）如果您是管理员，请参阅第22章以获取创建帐户的帮助。

---

您需要成为安装PostgreSQL的操作系统用户（通常是postgres）才能创建第一个用户帐户。
也可能是您分配了一个 与您的操作系统用户名 不同的PostgreSQL用户名；在这种情况下，您需要使用-U开关或设置PGUSER环境变量来指定您的PostgreSQL用户名。

如果您有一个用户帐户，但它没有创建数据库所需的权限，您将看到以下内容：

createdb: error: database creation failed: ERROR:  permission denied to create database

---

并非每个用户都有创建新数据库的授权。
如果PostgreSQL拒绝为您创建数据库，则站点管理员需要授予您创建数据库的权限。
如果发生这种情况，请咨询您的站点管理员。
如果您自己安装了PostgreSQL，则应使用您启动服务器时使用的用户帐户登录本教程。[[1]](https://www.postgresql.org/docs/14/tutorial-createdb.html#ftn.id-1.4.3.4.10.4)

您还可以使用其他名称创建数据库。
PostgreSQL允许您在给定站点创建任意数量的数据库。
数据库名称必须具有字母第一个字符，并且长度限制为63个字节。
一个方便的选择是创建一个与您当前用户名相同的数据库。
许多工具假定库名为默认值，因此它可以节省您的一些输入。
要创建该数据库，只需键入：

$ createdb

---

如果您不想再使用数据库，可以将其删除。
例如，如果您是数据库mydb的所有者（创建者），您可以使用以下命令销毁它：

$ dropdb mydb

---

（对于此命令，库名不默认为用户帐户名。
您始终需要指定它。）此操作会物理删除与数据库关联的所有文件，并且无法撤消，因此只能在经过大量深思熟虑后才能完成。

更多关于createdb和dropdb的信息可以分别在createdb和dropdb中找到。[1]
解释一下为什么这样做：PostgreSQL用户名与操作系统用户帐户是分开的。
当您连接到数据库时，您可以选择要连接的PostgreSQL用户名；如果不这样做，它将默认为与您当前的操作系统帐户相同的名称。
碰巧的是，总会有一个PostgreSQL用户帐户与启动服务器的操作系统用户同名，并且该用户始终有权创建数据库。
除了以该用户身份登录，您还可以在任何地方指定-U选项以选择要连接的PostgreSQL用户名。

---

1.4.访问数据库

创建数据库后，您可以通过以下方式访问它：

• 运行PostgreSQL交互式终端程序，称为psql，它允许您以交互方式输入、编辑和执行SQL命令。
• 使用现有的图形前端工具（如pgAdmin）或支持ODBC或JDBC的办公套件来创建和操作数据库。
  本教程未涵盖这些可能性。
• 使用几种可用的语言绑定之一编写自定义应用程序。
  第四部分将进一步讨论这些可能性。

---

您可能希望启动psql以尝试本教程中的示例。
可以通过键入以下命令为mydb数据库激活它：

$ psql mydb

---

如果您不提供库名，那么它将默认为您的用户帐户名。
您已经在上一节中使用createdb发现了此方案。

在psql中，您将收到以下消息：

psql (14.13)
Type "help" for help.

mydb=>

---

最后一行也可以是：

mydb=#

---

这意味着您是数据库超级用户，如果您自己安装了PostgreSQL实例，情况很可能就是这样。
成为超级用户意味着您不受访问控制。对于本教程的目的来说，这并不重要。

如果您在启动psql时遇到问题，请回到上一节。
createdb和psql的诊断相似，如果前者有效，后者也应该有效。

由psql打印出来的最后一行是提示符，它表示psql正在监听您，并且您可以在psql维护的工作空间中键入SQL查询。

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

mydb=>  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中。在本教程中，我们不会明确使用这些功能，但您可以在有帮助的时候自己使用它们。

---

第2章 SQL语言

---

2.1 介绍

本章概述了如何使用SQL来执行简单的操作。本教程只是给你一个介绍，绝不是关于SQL的完整教程。
已经写了许多关于SQL的书，包括[melt93]和[date97]。你应该知道一些PostgreSQL语言特性是标准的扩展。

在下面的示例中，我们假设您已经创建了一个名为mydb的数据库，如上一章所述，并且已经能够启动psql。

本手册中的示例也可以在PostgreSQL源代码发行版中的目录src/tutorial/中找到。（PostgreSQL的二进制发行版可能不提供这些文件。）

要使用这些文件，首先切换到该目录并运行make：

$ cd .../src/tutorial
$ make

---

这将创建脚本并编译包含用户定义函数和类型的C文件。
然后，要开始本教程，请执行以下操作：

$ psql -s mydb

...

mydb=> \i basics.sql

---

该\i命令从指定文件中读取命令。
psql的-s选项将您置于单步模式，在向服务器发送每个语句之前暂停。
本节中使用的命令位于文件basics.sql中。

---

2.2 概念

PostgreSQL是一个关系数据库管理系统（RDBMS）。
这意味着它是一个管理存储在关系中的数据的系统。
关系本质上是 table 的数学术语。
在表中存储数据的概念在今天如此普遍，以至于看起来本质上是显而易见的，但是还有许多其他组织数据库的方法。
类Unix操作系统上的文件和目录构成了分层数据库的一个例子。
更现代的发展是面向对象的数据库。

每个表都是行的命名集合。
给定表的每一行都有相同的命名列集，并且每一列都具有特定的数据类型。
尽管列在每一行中具有固定的顺序，但重要的是要记住，SQL不能以任何方式保证表中行的顺序（尽管它们可以显式排序以供显示）。

表被分组到数据库中，由单个PostgreSQL服务器实例 管理的数据库集合 构成数据库集群。

---

2.3 创建新表

您可以通过指定表名 以及所有列名 及其类型 来创建新表：

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    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');

---

如果您愿意，您可以按不同的顺序列出列，甚至可以省略某些列，例如，如果沉淀未知：

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;

---

这里*是“所有列”的简写。 [2]所以同样的结果是：

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子句是可选的。）

通过添加指定需要哪些行的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

---

在此示例中，排序顺序未完全指定，因此您可能会按任一顺序获取San Francisco行。
但是如果您这样做，您总是会得到上面显示的结果：

SELECT * FROM weather
    ORDER BY city, temp_lo;

---

您可以请求从查询结果中删除重复的行：

SELECT DISTINCT city
    FROM weather;

city
Hayward
San Francisco

(2 rows)

---

同样，结果行排序可能会有所不同。
您可以通过同时使用DISTINCT和ORDER BY来确保结果一致：[3]

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

---

[2]虽然SELECT *对于即兴查询很有用，但它在生产代码中被广泛认为是不好的风格，因为向表中添加一列会改变结果。

[3]在一些数据库系统中，包括旧版本的PostgreSQL，DISTINCT的实现会自动对行进行排序，因此不需要ORDER BY。
但这不是SQL标准所要求的，当前的PostgreSQL不能保证DISTINCT会导致行被排序。

---

2.6.表之间的连接

到目前为止，我们的查询一次只访问一个表。
查询可以一次访问多个表，或者以同时处理表的多行的方式访问同一个表。
一次访问多个表（或同一表的多个实例）的查询称为连接查询。
它们将一个表中的行与第二个表中的行组合在一起，并通过表达式指定要配对的行。
例如，要返回所有天气记录以及关联城市的位置，数据库需要将weather表每一行的city列与cities表中所有行的name列进行比较，并选择这些值匹配的行对。[4]

这将通过以下查询来完成：

SELECT * FROM weather JOIN cities ON 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城市没有结果行。
  这是因为Hayward的cities表中没有匹配的条目，所以连接忽略了weather表中不匹配的行。
  我们将很快看到如何修复这个问题。
• 有两列包含城市名称。
  这是正确的，因为weather表和cities表中的列列表是连接的。
  但是实际上这是不可取的，所以您可能希望显式列出输出列，而不是使用*：

SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather JOIN cities ON city = name;

---

由于列都有不同的名称，解析器会自动找到它们属于哪个表。
如果两个表中有重复的列名，您需要限定列名以显示您指的是哪一个，如下所示：

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather JOIN cities ON weather.city = cities.name;

---

在连接查询中限定所有列名被广泛认为是一种很好的风格，这样如果稍后将重复的列名添加到其中一个表中，查询就不会失败。

到目前为止看到的那种连接查询也可以用这种形式编写：

SELECT *
    FROM weather, cities
    WHERE city = name;

---

此语法早于SQL-92中引入的JOIN/ON语法。
表仅在FROM子句中列出，比较表达式添加到WHERE子句中。
这种较旧的隐式语法和较新的显式JOIN/ON语法的结果是相同的。
但是对于查询的读者来说，显式语法使其含义更容易理解：连接条件由其自己的关键字引入，而以前条件与其他条件一起混合到WHERE子句中。

现在我们将弄清楚如何让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)

---

此查询称为左向外连接，因为连接运算符左侧提到的表的每一行将至少在输出中出现一次，而右侧的表将仅输出与左侧表的某些行匹配的那些行。
当输出没有右表匹配的左表行时，将用空（空值）替换右表列。

练习：也有右外接和全外接。
试着找出它们的作用。

我们也可以针对表本身进行联接。
这称为自联接。
例如，假设我们希望查找其他天气记录的温度范围内的所有天气记录。
因此，我们需要将每个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 JOIN weather w2
        ON 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)

---

在这里，我们将天气表重新标记为w1和w2，以便能够区分连接的左侧和右侧。
您还可以在其他查询中使用这些类型的别名来节省一些输入，例如：

SELECT *
    FROM weather w JOIN cities c ON w.city = c.name;

---

你会经常遇到这种缩写风格。

---

[4]这只是一个概念模型，连接通常以比实际比较每一对可能的行更有效的方式执行，但这对用户来说是不可见的。

---

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);     WRONG

---

但是这不起作用，因为聚合max不能在WHERE中使用。
（存在此限制是因为WHERE子句确定哪些行将包含在聚合计算中；因此显然必须在计算聚合函数之前对其进行评估。）然而，通常情况下，可以重述查询以完成所需的结果，这里使用子查询：

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
     

city
San Francisco

(1 row)

---

这没问题，因为子查询是一个独立的计算，它与外部查询中发生的事情分开计算自己的聚合。

聚合与GROUP BY子句结合使用也非常有用。
例如，我们可以通过以下方式获得每个城市观测到的读数和最大低温：

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city;

city	count	max
Hayward	1	37
San Francisco	2	46

(2 rows)

---

这给了我们每个城市一个输出行。
每个聚合结果都是根据与该城市匹配的表行计算的。
我们可以使用HAVING过滤这些分组行：

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;
  city   | count | max
---------|-------|-----
 Hayward |     1 |  37
(1 row)

---

这只给了我们所有temp_lo值低于40的城市同样的结果。
最后，如果我们只关心名字以“S”开头的城市，我们可能会这样做：

SELECT city, count(*), max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city;

city	count	max
San Francisco	2	46

(1 row)

---

(1) 该LIKE运算符进行模式匹配，并在第9.7节中解释。

理解聚合和SQL的WHERE和HAVING子句之间的相互作用非常重要。
WHERE和HAVING之间的根本区别是：WHERE在计算组和聚合之前选择输入行（因此，它控制哪些行进入聚合计算），而HAVING在计算组和聚合之后选择组行。
因此，WHERE子句不能包含聚合函数；尝试使用聚合来确定哪些行将成为聚合的输入是没有意义的。
另一方面，HAVING子句总是包含聚合函数。
（严格来说，您可以编写一个不使用聚合的HAVING子句，但它很少有用。
同样的条件可以在WHERE阶段更有效地使用。）

在前面的示例中，我们可以在WHERE中应用城市名称限制，因为它不需要聚合。
这比将限制添加到HAVING更有效，因为我们避免对所有未通过WHERE检查的行进行分组和聚合计算。

选择进入聚合计算的行的另一种方法是使用FILTER，它是每个聚合选项：

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
    FROM weather
    GROUP BY city;

city	count	max
Hayward	1	37
San Francisco	1	46

(2 rows)

---

FILTER很像WHERE，只是它只从它所连接的特定聚合函数的输入中删除行。
在这里，count聚合只计算temp_lo低于45的行；但是max聚合仍然应用于所有行，因此它仍然找到46的读数。

---

2.8 更新

您可以使用UPDATE命令更新现有行。
假设您发现温度读数在11月28日之后都下降了2度。
您可以按如下方式更正数据：

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的天气感兴趣。
然后您可以执行以下操作以从表中删除这些行：

DELETE FROM weather WHERE city = '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将删除给定表中的所有行，使其为空。
在此之前，系统不会请求确认！

---

第3章 高级功能

---

3.1 介绍

在上一章中，我们已经介绍了使用SQL在PostgreSQL中存储和访问数据的基础知识。
我们现在将讨论SQL的一些更高级的功能，这些功能可以简化管理并防止数据丢失或损坏。
最后，我们将介绍一些PostgreSQL扩展。

本章有时会参考第2章中的示例来更改或改进它们，因此阅读该章会很有用。
本章中的一些示例也可以在教程目录的advanced.sql中找到。
该文件还包含一些要加载的示例数据，这里不再重复。（有关如何使用该文件，请参阅第2.1节。）

---

3.2 视图

请参考第2.6节中的查询。
假设您的应用程序对天气记录和城市位置的组合列表特别感兴趣，但您不想每次都键入查询。
您可以在查询上创建一个视图，它为查询提供一个名称，您可以像普通表一样引用它：

CREATE VIEW myview AS
    SELECT name, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

---

充分利用视图是良好SQL数据库设计的一个关键方面。
视图允许您将表结构的细节封装在一致的接口后面，这些细节可能会随着应用程序的发展而变化。

视图几乎可以在任何可以使用真实表的地方使用。
在其他视图上构建视图并不少见。

---

3.3.外键

回想第2章中的weather和cities表。
考虑以下问题：您要确保没有人可以在weather表中插入cities表中没有匹配条目的行。
这称为维护数据的引用完整性。
在简单的数据库系统中，这将通过首先查看cities表以检查是否存在匹配记录来实现（如果有的话），然后插入或拒绝新的weather记录。
这种方法有许多问题，非常不方便，所以PostgreSQL可以为您做这件事。

表的新声明如下所示：

CREATE TABLE cities (
        name     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(name),
        temp_lo   int,
        temp_hi   int,
        prcp      real,
        date      date
);

---

现在尝试插入无效记录：

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美元付款。
简化得离谱，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');

---

这些命令的细节在这里并不重要；重要的一点是，为了完成这个相当简单的操作，有几个单独的更新。
我们银行的官员希望得到保证，要么所有这些更新都发生了，要么都没有发生。
系统故障导致鲍勃收到了没有从爱丽丝那里借记的100美元，这当然是不行的。
如果没有鲍勃的贷记，爱丽丝也不会永远是一个快乐的顾客。
我们需要保证，如果操作中途出了问题，到目前为止执行的任何步骤都不会生效。
将更新分组到一个事务中就可以保证这一点。
一个事务被称为原子事务：从其他事务的角度来看，它要么完全发生，要么根本不发生。

我们还希望保证，一旦交易完成并被数据库系统确认，它确实已经被永久记录下来，即使此后不久发生崩溃也不会丢失。
例如，如果我们记录鲍勃的现金提取，我们不希望在他走出银行门后，借记到他账户的借记会在崩溃中消失。
事务数据库保证在交易报告完成之前，交易所做的所有更新都记录在永久存储中（即磁盘上）。

事务数据库的另一个重要特性与原子更新的概念密切相关：当多个事务同时运行时，每个事务都不应该能够看到其他事务所做的不完整的更改。
例如，如果一个事务正忙于汇总所有分支余额，那么它就不能包括从Alice分支的借方，而不包括从Bob分支的贷方，反之亦然。
因此，事务必须是全有或全无的，不仅在它们对数据库的永久影响方面，而且在它们发生时的可见性方面。
到目前为止，一个打开的事务所做的更新对其他事务是不可见的，直到事务完成，所有的更新同时变得可见。

在PostgreSQL中，通过用BEGIN和COMMIT命令包围事务的SQL命令来设置事务。
所以我们的银行事务实际上看起来像：

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;

---

如果在事务中途，我们决定不提交（也许我们刚刚注意到Alice的余额为负），我们可以发出命令ROLLBACK而不是COMMIT，到目前为止我们所有的更新都将被取消。

PostgreSQL实际上将每个SQL语句视为在事务中执行。
如果您不发出BEGIN命令，那么每个单独的语句都有一个隐式的BEGIN和（如果成功）COMMIT包装。
由BEGIN和COMMIT包围的一组语句有时称为事务块。

---

Note : 一些客户端库会自动发出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.窗口函数

窗口函数对一组与当前行相关的表行执行计算。
这与聚合函数可以完成的计算类型相当。
但是，窗口函数不会像非窗口聚合调用那样将行分组到单个输出行中。
相反，行保留其单独的身份。
在幕后，窗口函数能够访问的不仅仅是查询结果的当前行。

下面是一个示例，说明如何将每个员工的工资与其部门的平均工资进行比较：

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，在这种情况下，有一个包含所有行的分区。

还有一个与窗口函数相关的重要概念：对于每一行，其分区内都有一组行，称为窗口框架。某些窗口函数仅作用于窗框的行，而不是整个分区。默认情况下，如果提供了ORDER BY，则帧由从分区开始到当前行的所有行组成，再加上根据 ORDER BY子句等于当前行的任何后续行。当省略ORDER BY时，默认帧由分区中的所有行组成。5 以下是一个使用 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
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.22节、第7.2.5节和SELECT参考页面。[5]

有一些以其他方式定义窗口框架的选项，但本教程不涉及它们。有关详细信息，请参阅第4.2.8节。

---

3.6.继承

继承是一个来自面向对象数据库的概念。
它为数据库设计开辟了有趣的新可能性。

让我们创建两个表：一个表cities和一个表capitals。
自然，首都也是城市，所以当你列出所有城市时，你需要某种方法来隐式显示首都。
如果你真的很聪明，你可以发明一些这样的方案：

CREATE TABLE capitals (
  name       text,
  population real,
  elevation  int,    -- (in ft)
  state      char(2)
);

CREATE TABLE non_capitals (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

CREATE VIEW cities AS
  SELECT name, population, elevation FROM capitals
    UNION
  SELECT name, population, elevation FROM non_capitals;

---

就查询而言，这工作正常，但首先，当您需要更新多行时，它会变得很难看。

更好的解决方案是这样的：

CREATE TABLE cities (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2) UNIQUE NOT NULL
) INHERITS (cities);

---

在这种情况下，capitals 行从其 父城市继承cities所有列（name、population和elevation）。
列name的类型是text，这是可变长度字符串的本机PostgreSQL类型。
capitals表有一个附加列state，它显示其状态缩写。
在PostgreSQL中，一个表可以从零个或多个其他表继承。

例如，以下查询查找海拔超过500英尺的所有城市的名称，包括州首府：

SELECT name, elevation
  FROM cities
  WHERE elevation > 500;

---

哪个返回：

name	elevation
Las Vegas	2174
Mariposa	1953
Madison	845

(3 rows)

---

另一方面，以下查询查找海拔超过500英尺的所有非州首府城市：

SELECT name, elevation
    FROM ONLY cities
    WHERE elevation > 500;

name	elevation
Las Vegas	2174
Mariposa	1953

(2 rows)

---

这里cities之前的ONLY表示查询应该只在cities表上运行，而不是在继承层次结构中低于cities的表上运行。
我们已经讨论过的许多命令——SELECT、UPDATE和DELETE——支持这种ONLY表示法。

---

Note : 尽管继承经常很有用，但它没有与唯一约束或外键集成，这限制了它的有用性。
第5.10节了解更多细节。

---

3.7.结论

PostgreSQL有许多本教程介绍中没有涉及的特性，这些特性面向SQL的新用户。
本书的其余部分将更详细地讨论这些特性。

如果您觉得需要更多介绍性材料，请访问PostgreSQL网站以获取更多资源的链接。

---

伊织 2024-09-15