常见问题解答

来自 PostgreSQL wiki
跳转到导航跳转到搜索

此 Wiki 上的更多常见问题解答条目

此文档的翻译


平台特定问题

Windows 用户还应阅读 Windows 平台常见问题解答。还有 其他平台的常见问题解答。

常见问题

什么是 PostgreSQL？如何发音？什么是 Postgres？

PostgreSQL 的发音是 Post-Gres-Q-L。（对于那些好奇如何说“PostgreSQL”的人，可以 音频文件）。

PostgreSQL 是一个对象关系数据库系统，它拥有传统专有数据库系统的功能，并具有下一代 DBMS 系统的增强功能。PostgreSQL 是免费的，并且完整的源代码可用。

PostgreSQL 开发由一个由世界各地的志愿者开发人员组成的团队执行，他们通过互联网进行沟通。它是一个社区项目，不受任何公司的控制。要参与，请参阅 开发人员常见问题解答。

Postgres 是 PostgreSQL 的一个广泛使用的昵称。它是伯克利项目最初的名称，并且强烈建议使用它而不是其他昵称。如果你觉得“PostgreSQL”难以发音，可以称它为“Postgres”。

谁控制 PostgreSQL？

如果你正在寻找 PostgreSQL 的看门人、中央委员会或控制公司，放弃吧——没有。我们确实有一个核心委员会和 git 提交者，但这些小组更多地是用于管理目的，而不是控制。该项目由开发人员和用户的社区指导，任何人都可以加入。你只需要订阅邮件列表并参与讨论。（请参阅 开发人员常见问题解答，了解如何参与 PostgreSQL 开发。）

谁是 PostgreSQL 全球开发组？

“PGDG”是一个国际性的、非法人组织的个人和公司，他们为 PostgreSQL 项目做出了贡献。PostgreSQL 核心团队通常作为 PGDG 的发言人。

谁是 PostgreSQL 核心团队？

核心团队成员 是 PostgreSQL 的高级贡献者，他们在项目中担任特定的角色。

各种 PostgreSQL 基金会怎么样？

虽然 PostgreSQL 项目在美国、欧洲、巴西和日本利用非营利性公司进行筹款和项目协调，但这些实体不拥有 PostgreSQL 代码。

PostgreSQL 的许可证是什么？

PostgreSQL 在类似 BSD 和 MIT 的许可证下发布。基本上，它允许用户对代码做任何他们想做的事情，包括在没有源代码的情况下转售二进制文件。唯一的限制是，您不能因软件出现问题而将我们追究法律责任。还有一个要求是，此版权必须出现在所有软件副本中。以下是我们使用的许可证

PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS
ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO
PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

PostgreSQL 支持哪些平台？

一般来说，任何现代的 Unix 兼容平台都应该能够运行 PostgreSQL。最近经过明确测试的平台可以在构建农场中看到。文档在https://postgresql.ac.cn/docs/current/static/supported-platforms.html中包含有关支持平台的更多详细信息。

PostgreSQL 还在 Microsoft Windows NT 基于的操作系统（如 Windows XP、Vista、7、8、2003、2008 等）上原生运行。一个预打包的安装程序可在https://postgresql.ac.cn/download/windows获得。

存在 Windows 的 Cygwin 构建，但通常不建议使用；请改用本机 Windows 构建。如果您真的需要 Cygwin 用于客户端应用程序，可以使用 PostgreSQL 客户端库（libpq）的 Cygwin 构建连接到本机 Windows PostgreSQL。

在哪里可以获得 PostgreSQL？

有适用于各种操作系统和平台的二进制发行版；请参见我们的下载区域。

源代码可以通过网页浏览器或FTP获得。

最新的版本是什么？

PostgreSQL 的最新版本显示在我们网站的首页上。

我们通常每年发布一个主要版本，每隔几个月发布一个次要版本。次要版本通常在所有受支持的主要版本分支中同时发布。有关主要版本与次要版本的区别，请参见https://postgresql.ac.cn/support/versioning。

在哪里可以获得支持？

PostgreSQL 社区通过电子邮件为许多用户提供帮助。订阅电子邮件列表的主要网站是https://postgresql.ac.cn/community/lists/。通用列表或错误列表是一个不错的起点。为了获得最佳效果，请考虑在发布之前阅读报告问题指南，以确保您包含足够的信息来帮助您。

主要的 IRC 频道是 Libera 上的 #postgresql（irc.libera.chat）。在同一个网络上还存在一个西班牙语频道（#postgresql-es）、一个法语频道（#postgresqlfr）和一个巴西语频道（#postgresql-br）。EFNet 上还有一个 PostgreSQL 频道。

支持公司列表可在https://postgresql.ac.cn/support/professional_support获得。

如何提交错误报告？

访问 PostgreSQL 错误表单https://postgresql.ac.cn/support/submitbug，将您的错误报告提交给 pgsql-bugs 邮件列表。还要查看我们的 FTP 站点ftp://ftp.postgresql.org/pub/，看看是否有更新的 PostgreSQL 版本。

为了及时获得有用的回复，您必须阅读报告问题指南，以确保您包含完全理解和处理您的报告所需的信息。

使用错误表单提交或发布到任何 PostgreSQL 邮件列表的错误通常会生成以下回复之一

  • 这不是错误，以及原因
  • 这是一个已知错误，并且已经在 TODO 列表中
  • 错误已在当前版本中修复
  • 错误已修复，但尚未打包到官方版本中
  • 要求提供更详细的信息
  • 错误是新的。可能会发生以下情况
    • 将创建补丁并将其包含在下一个主要或次要版本中
    • 错误无法立即修复，并将其添加到 TODO 列表中

如何了解已知错误或缺失的功能？

PostgreSQL 支持 SQL:2008 的扩展子集。请参阅我们的TODO 列表，了解已知错误、缺失的功能和未来计划。

功能请求通常会产生以下回复之一

  • 该功能已经在 TODO 列表中
  • 该功能不受欢迎，因为
    • 它重复了已经遵循 SQL 标准的现有功能
    • 该功能会增加代码复杂性，但收益甚微
    • 该功能可能不安全或不可靠
  • 新功能已添加到 TODO 列表中

PostgreSQL 不使用错误跟踪系统，因为我们发现直接回复电子邮件并保持 TODO 列表更新更有效。实际上，错误不会在软件中持续很长时间，影响大量用户的错误会迅速修复。找到 PostgreSQL 版本中所有更改、改进和修复的唯一方法是阅读 git 日志消息。即使发布说明也没有列出对软件进行的每一个更改。

我遇到的错误已在更新的次要版本中修复，但我不想升级。我可以只获取此问题的补丁吗？

不。没有人会为您创建自定义补丁，这样您就可以（例如）从 8.4.3 中提取修复程序并将其应用于 8.4.1。这是因为永远不需要这样做。如果您真的觉得必须这样做，则需要从源代码中自己提取补丁。

PostgreSQL 有一项严格的政策，即根据版本策略，只有错误修复会被反向移植到点版本中。例如，从 8.4.1 升级到 8.4.3 是安全的。将保持二进制兼容性，不需要转储和重新加载，不会出现任何问题，但可能会导致问题的错误已修复。最糟糕的情况是，错误修复可能需要在更新后进行 REINDEX，在这种情况下，将在发布说明中说明这一点。即使您还没有遇到特定错误，您以后也可能会遇到，因此建议您尽快升级。您只需要安装更新并重新启动数据库服务器。建议您阅读发布说明，但很少需要执行任何特殊操作。

从 8.3 升级到 8.4，或从 8.4 升级到 9.0，是一个主要升级，没有相同的保证。但是，如果在 9.0 中发现错误，则通常会在所有维护的旧版本（如 8.4 和 8.3）中修复（“反向移植”）它，如果这样做是安全且可行的。

这意味着，如果您运行的是 8.1.0，强烈建议您升级到 8.1.21，并且非常安全。另一方面，升级到下一个主要版本 8.2.x 可能需要更改您的应用程序，并且肯定需要转储和重新加载或（对于 8.4+）pg_upgrade；请参阅文档中有关如何升级 PostgreSQL 集群的选项。

如果您想谨慎对待所有升级，您应该仔细阅读从当前版本到同一主要版本的最新次要版本之间每个点版本发布说明。如果您对升级特别担心，您可以从PostgreSQL 的 git 存储库获取每个点版本更改的源代码并检查它。

强烈建议您始终升级到最新的次要版本。避免尝试从点版本中提取和应用单个修复程序；这样做会绕过 PostgreSQL 团队在准备版本时进行的所有 QA，并创建您自己的自定义版本，该版本从未被任何人使用过。直接更新到最新的经过测试的安全版本要安全得多。修补您自己的自定义非标准版本还需要更多时间/精力，并且需要与正常升级相同的停机时间。

我有一个程序说它需要 PostgreSQL x.y.1。我可以使用 PostgreSQL x.y.2 吗？

任何与特定版本（如 8.4.1）一起使用的程序都应该与同一主要版本中的任何其他次要版本一起使用。这意味着，如果程序说它需要（例如）8.4.1，您可以也应该安装 8.4 系列中的最新版本。

如果您的应用程序供应商告诉您不同，请将他们引导到本常见问题解答，如果他们仍然不相信，请联系 pgsql-general 邮件列表，以便我们与他们联系。强制用户停留在旧的次要版本上对于安全性和数据完整性来说是危险的。

有关更多详细信息，请参见前面的问题。

有什么文档可用？

PostgreSQL 包含大量文档，包括大型手册、手册页和一些测试示例。请参见 /doc 目录。您也可以在https://postgresql.ac.cn/docs上在线浏览手册。

有许多 PostgreSQL 书籍可供购买；其中两本也可以在线获得。可以在https://postgresql.ac.cn/docs/books/找到书籍列表。其中最受欢迎的一本是 Korry & Susan Douglas 编写的。

Wiki 上还收集了 PostgreSQL 技术文章，请参阅维基。

命令行客户端程序 psql 有一些 \d 命令来显示有关类型、运算符、函数、聚合等的信息 - 使用 \? 显示可用命令。

如何学习 SQL？

首先，考虑上面提到的针对 PostgreSQL 的书籍。许多用户也喜欢《实用 SQL 手册》(The Practical SQL Handbook)，作者为 Bowman, Judith S. 等，出版商为 Addison-Wesley。另一些人喜欢《SQL 全面参考》(The Complete Reference SQL)，作者为 Groff 等，出版商为 McGraw-Hill。

许多人认为 PostgreSQL 文档是学习 SQL 本身以及 PostgreSQL 如何实现 SQL 的优秀指南。为了获得最佳效果，在学习时，将 PostgreSQL 与另一个功能齐全的 SQL 数据库一起使用，这样您就可以习惯 SQL，而不会依赖于 PostgreSQL 特定的功能。PostgreSQL 文档通常会说明哪些功能是 PostgreSQL 对标准的扩展。

还有许多不错的在线教程可用。

如何提交补丁或加入开发团队？

请参阅 开发人员常见问题解答。

PostgreSQL 与其他 DBMS 相比如何？

衡量软件有几种方法：功能、性能、可靠性、支持和价格。

功能

PostgreSQL 拥有大型专有 DBMS 中大多数功能，例如事务、子查询、触发器、视图、外键参照完整性和复杂的锁定。我们还拥有某些它们没有的功能，例如用户定义类型、继承、规则和多版本并发控制以减少锁定冲突。其他功能（地理空间 功能、时间 功能、GUI 接口 等）可通过第三方或 PostgeSQL 提供的扩展 获得。

性能

PostgreSQL 的性能与其他专有和开源数据库相当。它在某些方面更快，在另一些方面则更慢。我们的性能通常与其他数据库相比在正负 10% 之内。

可靠性

我们认识到 DBMS 必须可靠，否则毫无价值。我们努力发布经过良好测试的稳定代码，其中包含最少的错误。每个版本至少有一个月的 beta 测试，我们的发布历史表明，我们可以提供稳定、可靠的版本，这些版本已准备好投入生产使用。我们相信我们在这一领域与其他数据库软件相比具有优势。

支持

我们的邮件列表为开发者和用户提供了一个大型社区，以帮助解决遇到的任何问题。虽然我们不能保证能修复问题，但专有 DBMS 也不能总是提供修复。直接访问开发者、用户社区、手册和源代码通常使 PostgreSQL 支持优于其他 DBMS。对于需要商业支持的人员，可以提供商业化的按事件支持。（参见 第 1.7 节）。

价格

我们对所有用途（包括专有和开源）都是免费的。您可以将我们的代码添加到您的产品中，没有任何限制，除了我们上面提到的 BSD 样式许可证中所述的限制。

PostgreSQL 可以嵌入吗？

PostgreSQL 被设计为一个客户端/服务器架构，它要求为每个客户端和服务器以及各种辅助进程使用单独的进程。许多嵌入式架构可以支持这些要求。但是，如果您的嵌入式架构要求数据库服务器在应用程序进程中运行，则无法使用 Postgres，应选择更轻量级的数据库解决方案。

流行的嵌入式选项包括 SQLite 和 Firebird SQL。

如何取消订阅 PostgreSQL 邮件列表？如何避免收到重复的电子邮件？

PostgreSQL Majordomo 页面允许订阅或取消订阅任何 PostgreSQL 邮件列表。（您可能需要将您的 Majordomo 密码发送到您的电子邮件地址才能登录。）

所有 PostgreSQL 邮件列表都已配置，以便群组回复发送到邮件列表和原始电子邮件作者。这样做是为了让用户能够最快地收到电子邮件回复。如果您希望在您已经直接收到电子邮件的情况下不再从列表中收到重复的电子邮件，请在 Majordomo 更改设置页面中选中 eliminatecc。您还可以通过取消选中 selfcopy 来防止收到您发布到列表中的电子邮件的副本。

用户客户端问题

PostgreSQL 有哪些接口可用？

PostgreSQL 核心源代码仅包含 C 和嵌入式 C 接口。所有其他接口都是独立的项目，需要单独下载；作为独立项目，它们可以拥有自己的发布计划和开发团队。

许多 PostgreSQL 安装程序将语言客户端接口（如 PgJDBC、nPgSQL、Pg ruby gem、psycopg2 for Python、DBD::Pg for Perl 等）捆绑到 PostgreSQL 安装程序中，或者提供下载选项。此外，某些编程语言运行时预装了 PostgreSQL 客户端库。

在 Linux 系统上，您通常可以使用包管理器直接安装语言绑定，如 psycopg2。

使用 PostgreSQL 与网页有哪些工具可用？

在 http://www.webreview.com 可以看到一个关于数据库支持网页的良好介绍。

对于 Web 集成，PHP (https://php.ac.cn) 是一个优秀的接口。

对于复杂的情况，许多人使用 Perl 和 DBD::Pg 以及 CGI.pm 或 mod_perl。

PostgreSQL 有图形用户界面吗？

有大量专有和开源开发者为 PostgreSQL 提供的 GUI 工具。您可以在 PostgreSQL GUI 工具社区指南 中找到详细的列表。

许多安装程序将图形化 PgAdmin-III 客户端捆绑到安装程序中，或者使用 Ecosystem:dbForge Studio for PostgreSQL。

管理问题

从源代码安装时，如何将 PostgreSQL 安装到 /usr/local/pgsql 之外的某个位置？

在运行 configure 时指定 --prefix 选项。

我正在 Windows 或 OS X 上安装 PostgreSQL，但不知道 postgres 用户的密码。

Dave Page 写了一篇 博文，解释了不同密码的用途，以及如何解决常见问题，例如重置密码。

PostgreSQL 9.2 现在在 Windows 上以 NETWORKSERVICE 身份运行，因此不再需要服务帐户密码，它只有“postgres”数据库用户的密码。

如何控制来自其他主机的连接？

默认情况下，PostgreSQL 仅允许来自本地计算机使用 Unix 域套接字或 TCP/IP 连接的连接。其他计算机将无法连接，除非您修改 postgresql.conf 文件中的 listen_addresses，通过修改 $PGDATA/pg_hba.conf 文件启用基于主机的身份验证，并重新启动数据库服务器。

如何调整数据库引擎以提高性能？

有三个主要领域可以提高性能。

查询更改

这涉及修改查询以获得更好的性能。

  • 创建索引，包括表达式索引和部分索引。
  • 使用 COPY 而不是多个 INSERT。
  • 将多个语句组合到单个事务中以减少提交开销。
  • 在从索引检索许多行时使用 CLUSTER。
  • 使用 LIMIT 返回查询输出的子集。
  • 使用预备语句。
  • 使用 ANALYZE 来维护准确的优化器统计信息。
  • 定期使用 VACUUM 或 pg_autovacuum。
  • 在进行大型数据更改时删除索引。

服务器配置

许多 postgresql.conf 设置会影响性能。有关更多详细信息，请参见管理指南/服务器运行时环境/运行时配置。

硬件选择

硬件对性能的影响在 http://momjian.us/main/writings/pgsql/hw_performance/index.html 中进行了详细说明。

有哪些调试功能可用？

在 https://postgresql.ac.cn/docs/current/interactive/runtime-config-logging.html 中有许多 log_* 服务器配置变量，这些变量可以启用查询和进程统计信息的打印，这对于调试和性能测量非常有用。

为什么我在尝试连接时会收到“对不起，客户端过多”的错误消息？

您已达到默认的 100 个数据库会话限制。有关是否应该提高连接限制或添加连接池的建议，请参阅 数据库连接数。

PostgreSQL 的升级流程是什么？

有关升级的一般讨论，请参阅 https://postgresql.ac.cn/support/versioning，有关升级 PostgreSQL 集群的具体说明，请参阅 https://postgresql.ac.cn/docs/current/static/upgrading.html。

如果您使用的是安装程序，请查看是否有与您使用的 PostgreSQL 安装程序相关的具体说明。

PostgreSQL 会处理各个国家最近的夏令时变化吗？

PostgreSQL 8.0 及更高版本依赖于广泛使用的 tzdata 数据库（也称为 zoneinfo 数据库或 Olson 时区数据库）来获取夏令时信息。要处理影响您的夏令时法律更改，请安装新的 tzdata 文件集并重新启动服务器。

所有 PostgreSQL 更新版本都包含最新的 tzdata 文件，因此对于您的主要版本，保持最新的次要版本通常就足够了。

在定期接收软件更新（包括新的 tzdata 文件）的平台上，可能更方便依赖系统中的 tzdata 文件副本。这可以通过编译时选项实现。大多数 Linux 发行版在其预构建的 PostgreSQL 版本中选择了这种方法。

8.0 之前的 PostgreSQL 版本始终依赖于操作系统的时区信息。

我应该使用什么计算机硬件？

由于 PC 硬件大多兼容，人们往往认为所有 PC 硬件质量都一样。事实并非如此。ECC 内存、高质量硬盘/固态硬盘、可靠的电源和优质主板比廉价硬件更可靠，性能也更好。PostgreSQL 几乎可以在任何硬件上运行，但如果可靠性和性能很重要，建议您仔细研究硬件选项。

与许多其他应用程序不同，数据库服务器通常受 I/O 和内存限制，因此建议首先关注 I/O 子系统，然后是内存容量，最后考虑 CPU 问题。高质量、高性能固态硬盘通常是提高数据库性能最便宜的方法。我们的邮件列表可以用来讨论硬件选项和权衡。

PostgreSQL 如何使用 CPU 资源？

PostgreSQL 服务器是基于进程的（而不是基于线程的）。每个数据库会话都连接到一个 PostgreSQL 操作系统 (OS) 进程。操作系统会自动将多个会话分散到所有可用的 CPU 上。操作系统还使用 CPU 处理磁盘 I/O 并运行其他非数据库任务。客户端应用程序可以使用线程，每个线程都连接到一个单独的数据库进程。从 9.6 版本开始，某些查询的部分可以并行运行，在单独的操作系统进程中，允许使用多个 CPU 内核。并行查询在 10 版本中默认启用（max_parallel_workers_per_gather），预计在未来版本中将实现更多并行性。

为什么 PostgreSQL 即使在空闲时也拥有如此多的进程？

如上面回答所述，PostgreSQL 基于进程，因此它为每个连接启动一个 postgres（或 Windows 上的 postgres.exe）实例。postmaster（它接受连接并为它们启动新的 postgres 实例）始终在运行。此外，PostgreSQL 通常有一个或多个“辅助”进程，如统计收集器、后台写入器、自动清理守护进程、walsender 等，所有这些进程在大多数系统监控工具中都显示为“postgres”实例。

尽管进程数量很多，但实际上它们使用的真实资源很少。请参阅下一个答案。

为什么 PostgreSQL 使用如此多的内存？

尽管看起来很多，但这绝对是正常的，实际上使用的内存并没有像 top 或 Windows 进程监视器所说的那样多。

像 top 和 Windows 进程监视器这样的工具可能会显示许多 postgres 实例（如上所述），每个实例似乎都使用大量的内存。通常，当将 postgres 实例使用的内存加起来时，总量是计算机实际安装内存的几倍！

这是这些工具报告内存使用方式的结果。它们通常对共享内存的理解并不透彻，并将其显示为每个 postgres 实例单独且排他地使用的内存。PostgreSQL 使用一大块共享内存来在其后端之间进行通信并缓存数据。由于这些工具将该共享内存块计数为每个 postgres 实例一次，而不是将它计数为所有 postgres 实例一次，因此它们严重高估了 PostgreSQL 使用的内存量。

此外，这些工具的许多版本在单个实例启动时不会立即报告整个共享内存块正在使用，而是统计它自启动以来触碰的共享页面的数量。在一个实例的生命周期中，它不可避免地会触碰越来越多的共享内存，直到它触碰了每一页，因此它报告的使用量将逐渐增加，最终包含整个共享内存块。这经常被误解为内存泄漏；但事实并非如此，这只是一个报告问题。

操作问题

如何仅选择查询的前几行？如何选择随机行？

要仅检索几行，如果您在 SELECT 时知道所需的行的数量，请使用 LIMIT。如果索引与 ORDER BY 相匹配，则可能不需要执行整个查询。如果您在 SELECT 时不知道行的数量，请使用游标和 FETCH。

要选择随机行，请使用

SELECT col
FROM tab
ORDER BY random()
LIMIT 1;

另请参阅此由 Andrew Gierth 编写的博客文章，其中包含有关此主题的更多信息。

如何找出定义了哪些表、索引、数据库和用户？如何查看 psql 用于显示它们的查询？

使用 \dt 命令在 psql 中查看表。要获取 psql 中所有命令的完整列表，您可以使用 \?。或者，您也可以阅读 psql 源代码文件 pgsql/src/bin/psql/describe.c，其中包含生成 psql 反斜杠命令输出的 SQL 命令。您还可以使用 -E 选项启动 psql，以便它打印出用于执行您给定命令的查询。PostgreSQL 还提供了一个 SQL 兼容的 INFORMATION SCHEMA 接口，您可以查询该接口以获取有关数据库的信息。

还有一些以 pg_ 开头的系统表也可以描述这些内容。

使用 psql -l 将列出所有数据库。

还可以尝试文件 pgsql/src/tutorial/syscat.source。它演示了从数据库系统表获取信息所需的许多 SELECT。

如何更改列的数据类型？

从 8.0 版本开始，您可以使用 ALTER TABLE ALTER COLUMN TYPE 轻松更改列的数据类型。

在早期版本中，请执行以下操作

BEGIN;
ALTER TABLE tab ADD COLUMN new_col new_data_type;
UPDATE tab SET new_col = CAST(old_col AS new_data_type);
ALTER TABLE tab DROP COLUMN old_col;
COMMIT;

然后，您可能需要执行 VACUUM FULL tab 以回收过期行使用的磁盘空间。

行、表和数据库的最大尺寸是多少？

以下是限制

Maximum size for a database? unlimited (32 TB databases exist)
Maximum size for a table? 32 TB
Maximum size for a row? 400 GB
Maximum size for a field? 1 GB
Maximum number of rows in a table? unlimited
Maximum number of columns in a table? 250-1600 depending on column types
Maximum number of indexes on a table? unlimited

当然，这些并不完全是无限的，而是受可用磁盘空间和内存/交换空间的限制。当这些值异常大时，性能可能会下降。

32 TB 的最大表大小不需要操作系统的大文件支持。大表存储为多个 1 GB 文件，因此文件系统大小限制并不重要。

通过将默认块大小增加到 32k，可以将最大表大小、行大小和最大列数增加四倍。还可以使用表分区来增加最大表大小。

一个限制是，不能在超过约 2,000 个字符的列上创建索引。幸运的是，这种索引很少需要。唯一性最好通过长列的 MD5 哈希的函数索引来保证，全文索引允许在列中搜索单词。

请注意，如果您存储一个表，其行的大小超过 2KB（每行中数据的总大小），那么“表中行的最大数量”可能限制为 40 亿或更少，请参阅 TOAST。

从典型文本文件存储数据需要多少数据库磁盘空间？

PostgreSQL 数据库可能需要多达五倍的磁盘空间来存储来自文本文件的数据。

例如，考虑一个包含 100,000 行的文件，每行包含一个整数和一个文本描述。假设文本字符串的平均长度为 20 个字节。该平面文件将为 2.8 MB。包含此数据的 PostgreSQL 数据库文件的大小可以估计为 5.2 MB

 24 bytes: each row header (approximate)
 24 bytes: one int field and one text field
+ 4 bytes: pointer on page to tuple
----------------------------------------
 52 bytes per row

PostgreSQL 中的数据页大小为 8192 字节 (8 KB)，因此

8192 bytes per page
-------------------  =  158 rows per database page (rounded down)
  52 bytes per row

 100000 data rows
------------------  =  633 database pages (rounded up)
 158 rows per page

633 database pages * 8192 bytes per page  =  5,185,536 bytes (5.2 MB)

索引不需要太多开销，但确实包含正在索引的数据，因此它们也可能很大。

NULL 存储为位图，因此它们使用的空间很少。

请注意，长值可能会被透明地压缩。

另请参阅有关此主题的演示文稿：File:How Long Is a String.pdf。

为什么我的查询很慢？为什么它们不使用我的索引？

索引对于 PostgreSQL 至关重要，通常用于提高数据库性能。但是，索引会给数据库系统带来额外的开销，这意味着应合理使用它们。您可以查看此有用的教程，以正确了解它们的具体内容，以便能够创建最适合您的特定情况的任何特定索引 PostgreSQL 索引

并非每个查询都会使用索引。只有在表大于最小大小且查询仅选择表中一小部分行的情况下，才会使用索引。这是因为索引扫描导致的随机磁盘访问可能比直接读取表或顺序扫描慢。

为了确定是否应该使用索引，PostgreSQL 必须拥有有关表的统计信息。这些统计信息是使用 VACUUM ANALYZE 或简单地 ANALYZE 收集的。使用统计信息，优化器可以知道表中有多少行，并且可以更好地确定是否应该使用索引。统计信息在确定最佳连接顺序和连接方法方面也很有价值。应定期执行统计信息收集，因为表的内容会发生变化。

索引通常不用于 ORDER BY 或执行连接。顺序扫描后进行显式排序通常比对大型表进行索引扫描快。但是，LIMIT 与 ORDER BY 组合使用通常会使用索引，因为只返回表的一小部分。

如果您认为优化器在选择顺序扫描时不正确，请使用 SET enable_seqscan TO 'off' 并再次运行查询，看看索引扫描是否确实更快。

在使用通配符运算符（如 LIKE 或 ~）时，索引只能在某些情况下使用

  • 搜索字符串的开头必须锚定到字符串的开头，即
    • LIKE 模式不能以 % 或 _ 开头。
    • ~（正则表达式）模式必须以 ^ 开头。
  • 搜索字符串不能以字符类开头，例如 [a-e]。
  • 不区分大小写的搜索（如 ILIKE 和 ~*）不会使用索引。相反，请使用表达式索引，这些索引在部分 4.8 中进行了描述。
  • 在 initdb 期间必须使用 C 本地化，因为非 C 本地化中的排序通常与 LIKE 的行为不匹配。您可以创建一个特殊的 text_pattern_ops 索引，它将在这种情况下起作用，但请注意，它只对 LIKE 索引有用。

也可以使用全文索引进行词语搜索。

请注意，有时会创建索引，但标记为“损坏”，因此不会使用，请参阅此处。

SlowQueryQuestions 文章包含更多提示和指南。

如何查看查询优化器如何评估我的查询？

这可以通过 EXPLAIN 命令来完成；请参见 使用 EXPLAIN。或者你可以使用查询分析工具来获取 PostgreSQL 执行计划 和查询执行统计信息，以可视化的格式呈现。

如何更改文本数据的排序顺序？

PostgreSQL 根据当前区域设置定义的排序顺序对文本数据进行排序，该区域设置在 initdb 期间选择。（在 8.4 及更高版本中，可以在创建新数据库时选择不同的区域设置。）如果你不喜欢这种排序顺序，则需要使用不同的区域设置。特别地，除 "C" 之外的多数区域设置都根据字典顺序进行排序，该顺序在很大程度上忽略了标点符号和空格。如果你不希望这样做，则需要使用 "C" 区域设置。

如何执行正则表达式搜索和不区分大小写的正则表达式搜索？如何为不区分大小写的搜索使用索引？

~ 运算符执行正则表达式匹配，而 ~* 执行不区分大小写的正则表达式匹配。LIKE 的不区分大小写变体称为 ILIKE。

不区分大小写的等式比较通常表示为

SELECT *
FROM tab
WHERE lower(col) = 'abc';

这将不会使用 "col" 上的标准索引。但是，如果你在 "lower(col)" 上创建表达式索引，它将被使用

CREATE INDEX tabindex ON tab (lower(col));

如果上面的索引被创建为 UNIQUE，那么该列可以存储大写和小写字符，但它不能包含仅在大小写方面不同的相同值。要强制在列中存储特定的大小写，请使用 CHECK 约束或触发器。

在 PostgreSQL 8.4 及更高版本中，你还可以使用贡献的 CITEXT 数据类型，它在内部实现 "lower()" 调用，因此你可以有效地将其视为完全不区分大小写的数据类型。CITEXT 也可以 用于 8.3，而更早的版本在 8.2 及更早版本中仅对 ASCII 字符进行不区分大小写处理，可以在 pgFoundry 上获得。

在查询中，如何检测字段是否为 NULL？如何连接可能的 NULL 值？如何根据字段是否为 NULL 进行排序？

你可以使用 IS NULL 或 IS NOT NULL 来测试该值，例如

SELECT *
FROM tab
WHERE col IS NULL;

将 NULL 与其他内容连接起来会产生另一个 NULL。如果这不是你想要的，你可以使用 COALESCE() 替换 NULL 值，例如

SELECT COALESCE(col1, '') || COALESCE(col2, '')
FROM tab;

要按 NULL 状态排序，请在 ORDER BY 子句中使用 IS NULL 或 IS NOT NULL 测试。为真的值将排序在为假的值之上，因此以下将把 NULL 条目放在输出的前面

SELECT *
FROM tab
ORDER BY (col IS NOT NULL), col;

在 PostgreSQL 8.3 及更高版本中，你还可以使用最近标准化的 NULLS FIRST/NULLS LAST 修饰符来控制 NULL 值的排序顺序，例如

SELECT *
FROM tab
ORDER BY col NULLS FIRST;

各种字符类型有什么区别？

类型 内部名称 备注
VARCHAR(n) varchar 大小指定最大长度，不填充
CHAR(n) bpchar 用空格填充到指定的固定长度
TEXT text 对长度没有特定上限
BYTEA bytea 可变长度字节数组（空字节安全）
"char"（带引号） char 一个字节

在检查系统目录和一些错误消息时，你会看到内部名称。

上面前四种类型是 "varlena" 类型（即，字段长度在磁盘上显式存储，其后是数据）。因此，实际使用的空间略大于预期大小。但是，长值也会受到压缩，因此磁盘上的空间也可能小于预期。

如果应用程序需要对字符串长度有特定的上限，则当存储可变长度字符串时，VARCHAR(n) 是最好的选择。TEXT 用于存储长度“无限”的字符串（尽管 PostgreSQL 中的所有字段都受最大值长度为 1 千兆字节的限制）。

CHAR(n) 用于存储所有长度相同的字符串。CHAR(n) 用空格填充到指定的长度，而 VARCHAR(n) 仅存储提供的字符。BYTEA 用于存储二进制数据，尤其是包含零字节的值。所有这些类型的性能特征都类似，除了 CHAR(n) 中涉及的空格填充需要额外的存储空间和一些额外的运行时。

"char" 类型（引号是必需的，以将其与 CHAR(n) 区分开来）是一种专门的数据类型，可以存储一个字节。它存在于系统目录中，但通常不建议在用户表中使用它。

如何创建一个 serial/自动递增字段？

PostgreSQL 支持 SERIAL 数据类型。实际上，这并不是真正的类型。它是一种创建从序列中获取值的整数列的简写方式。

例如，这

CREATE TABLE person (
    id SERIAL,
    name TEXT
);

将自动转换为

CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
    id INTEGER NOT NULL DEFAULT nextval('person_id_seq'),
    name TEXT
);

自动创建的序列命名为 table_serialcolumn_seq，其中 table 和 serialcolumn 分别是表和 SERIAL 列的名称。有关序列的更多信息，请参见 CREATE SEQUENCE 手册页。

还有 BIGSERIAL，它类似于 SERIAL，只是生成的列类型为 BIGINT 而不是 INTEGER。如果你认为在表的整个生命周期内可能需要超过 20 亿个序列值，请使用此类型。

请注意，序列可能包含“空洞”或“间隙”，这是操作的正常部分。生成的键为 1、4、5、6、9、... 等等是完全正常的。请参见 有关序列间隙的常见问题解答条目。

如何获取 SERIAL 插入的值？

最简单的方法是使用 RETURNING 获取分配的 SERIAL 值。使用上一个问题中的示例表，它将如下所示

INSERT INTO person (name) VALUES ('Blaise Pascal') RETURNING id;

你还可以调用 nextval() 并将该值用于 INSERT，或者在 INSERT 之后调用 currval()。

currval() 不会导致与其他用户的竞争条件吗？

不会。currval() 返回你的会话分配的最新序列值，与其他会话中发生的事情无关。

为什么我的序列/SERIAL 列编号中有间隙？为什么我的序列号在事务中止时没有被重用？

为了提高并发性，序列值是在需要时分配给正在运行的事务的；序列对象不会被锁定，而是立即可供另一个事务获取另一个序列值。这会导致中止事务的编号中出现间隙，如 nextval() 函数的 NOTE 部分 中所述。

此外，不干净的服务器关闭会导致序列在恢复时递增，因为 PostgreSQL 会保留一个序列号缓存来分发，并且在不干净的关闭中，它不确定哪些缓存的数字已经使用过。由于序列允许存在间隙，因此它会采取安全措施，递增序列。

序列中出现间隙的另一个原因是在 CREATE SEQUENCE 中使用 CACHE 子句。

通常，你不应该依赖 SERIAL 键或 SEQUENCE 不会出现间隙，也不应该对它们的顺序做出假设；不保证 id n+1 在 id n 之后插入，除非两者都在同一事务中生成。将合成键进行比较以判断是否相等，并且仅比较是否相等。

无间隙序列是可能的，但对性能非常不利。一次最多只能有一个事务从无间隙序列中插入行。没有内置的 SERIAL 或 SEQUENCE 等效于无间隙序列，但 实现起来很简单。有关无间隙序列实现的信息可以在邮件列表存档、Stack Overflow 以及 这篇有用的文章 中找到。除非是绝对的业务需求，否则避免使用无间隙序列。考虑按需动态生成无间隙编号以进行显示，使用 row_number() 窗口函数，或将其添加到定期运行的批处理过程中。

另请参见：常见问题解答：在 PostgreSQL 中使用序列。

什么是 OID？

如果表以 WITH OIDS 创建，则每行都会包含一个 OID 列，该列在 INSERT 期间自动填充。OID 是顺序分配的 4 字节整数。最初，它们在整个安装中是唯一的。但是，OID 计数器在 40 亿处循环，此后 OID 可能会重复。

可以通过在 OID 列上创建唯一索引来防止在单个表中重复 OID（但请注意，WITH OIDS 子句本身不会创建这样的索引）。系统会检查索引以查看新生成的 OID 是否已存在，如果存在，则会生成新的 OID 并重复。只要没有包含 OID 的表拥有超过 40 亿行的很小一部分，这就能很好地工作。

PostgreSQL 在系统目录中使用 OID 作为对象标识符，在系统目录中，大小限制不太可能成为问题。

要唯一编号用户表中的行，最好使用 SERIAL 而不是 OID 列，如果表预计在其生命周期内拥有超过 20 亿个条目，则使用 BIGSERIAL。

什么是 CTID？

CTID 通过表中的块和偏移位置标识特定的物理行。它们被索引条目用来指向物理行。逻辑行的 CTID 在更新时会发生变化，因此 CTID 不能用作长期行标识符。但它有时可用于在事务中标识行，此时不预计会发生竞争更新。

为什么我收到错误“ERROR: Memory exhausted in AllocSetAlloc()”？

你可能在系统上用完了虚拟内存，或者你的内核对某些资源的限制很低。在启动服务器之前尝试以下操作

ulimit -d 262144
limit datasize 256m

根据您的 shell，这些命令中可能只有一个会成功，但它会将您的进程数据段限制设置得更高，并可能允许查询完成。此命令适用于当前进程以及在运行该命令后创建的所有子进程。如果您在 SQL 客户端遇到问题，因为后端返回了过多的数据，请在启动客户端之前尝试使用此命令。

如何确定我正在运行的 PostgreSQL 版本？

运行以下查询：SELECT version();

有没有办法留下数据库操作的审计跟踪？

没有内置功能，但您可以自己构建这样的功能。

官方文档中的简单示例：https://postgresql.ac.cn/docs/current/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE

针对此功能的项目：https://postgresql.ac.cn/ftp/projects/pgFoundry/tablelog/

背景信息和其他示例实现：http://it.toolbox.com/blogs/database-soup/simple-data-auditing-19014 http://www.go4expert.com/forums/showthread.php?t=7252 http://www.alberton.info/postgresql_table_audit.html

如何创建默认值为当前时间的列？

使用 CURRENT_TIMESTAMP

CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

如何执行外连接？

PostgreSQL 使用 SQL 标准语法支持外连接。以下列举两个示例：

SELECT *
FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);

或

SELECT *
FROM t1 LEFT OUTER JOIN t2 USING (col);

这些相同的查询将 t1.col 连接到 t2.col，并返回 t1 中任何未连接的行（那些在 t2 中没有匹配的行）。RIGHT 连接将添加 t2 中未连接的行。FULL 连接将返回匹配的行以及来自 t1 和 t2 的所有未连接的行。OUTER 这个词是可选的，在 LEFT、RIGHT 和 FULL 连接中是默认的。普通连接称为 INNER 连接。

如何使用多个数据库执行查询？

虽然没有办法直接查询当前数据库以外的数据库，但有多种方法可以解决这个问题，其中一些方法将在下面介绍。

PostgreSQL 中的 SQL/MED 支持允许创建“外部数据包装器”，将远程数据库中的表链接到本地数据库。远程数据库可能是同一个 PostgreSQL 实例上的另一个数据库，也可能是世界另一端的数据库，这并不重要。 postgres_fdw 内置于 PostgreSQL 9.3 中，包括读写支持；一个 适用于 9.2 的只读版本 可以编译并作为 contrib 模块安装。

contrib/dblink 允许使用函数调用进行跨数据库查询，并且适用于更旧的 PostgreSQL 版本。与 postgres_fdw 不同，它不能将条件“下推”到远程服务器，因此它通常会最终提取比您需要的更多数据。

当然，客户端也可以同时连接到不同的数据库，并在客户端合并结果。

如何从函数返回多行或多列？

使用返回集合函数很容易，从 PL/pgSQL 函数返回多行数据.

为什么在 PL/PgSQL 函数中访问临时表时会收到“关系与 OID ##### 不存在”的错误？

在 PostgreSQL 版本 < 8.3 中，PL/PgSQL 会缓存函数脚本，一个不幸的副作用是，如果一个 PL/PgSQL 函数访问一个临时表，而该表后来被删除并重新创建，并且该函数再次被调用，该函数将失败，因为缓存的函数内容仍然指向旧的临时表。解决方案是在 PL/PgSQL 中使用 EXECUTE 访问临时表。这将导致查询在每次调用时重新解析。

这个问题不会出现在 PostgreSQL 8.3 及更高版本中。

有哪些复制解决方案可用？

虽然“复制”是一个单一的术语，但有几种技术可以进行复制，每种技术都有其优点和缺点。我们的文档在 https://postgresql.ac.cn/docs/current/static/high-availability.html 中对这个主题进行了很好的介绍，并在 复制、集群和连接池 中列出了复制软件和功能。

主从复制允许单个主服务器接收读写查询，而从服务器只能接收读/SELECT 查询。最流行的免费主从 PostgreSQL 复制解决方案是 Slony-I。

多主复制允许将读写查询发送到多个复制计算机。此功能也会对性能产生严重影响，因为需要同步服务器之间的更改。PGCluster 是最流行的此类解决方案，可以免费用于 PostgreSQL。

还有一些专有和基于硬件的复制解决方案可用，支持多种复制模型。

是否可以创建共享存储的 PostgreSQL 服务器集群？

PostgreSQL 不支持使用 共享存储 在 SAN、SCSI 后端、iSCSI 卷或其他共享媒体上进行集群。这种“RAC 式”集群不受支持。目前只支持基于复制的集群。

有关详细信息，请参阅 复制、集群和连接池 信息。

共享存储 '故障转移' 是可能的，但是，在同一时间运行并访问数据存储的 postmaster 超过一个是不安全的。心跳和 STONITH 或其他一些硬断开连接选项是推荐的。

为什么我的表名和列名在我的查询中不被识别？为什么大小写不保留？

名称无法识别最常见的原因是在创建表时在表名或列名周围使用双引号。当使用双引号时，表名和列名（称为标识符）将以区分大小写的方式存储，这意味着您必须在查询中引用名称时使用双引号。一些界面，如 pgAdmin，会在创建表时自动将标识符用双引号括起来。因此，要识别标识符，您必须：

  • 创建表时避免使用双引号括起标识符
  • 在标识符中只使用小写字符
  • 在查询中引用标识符时使用双引号括起它们

我忘记了数据库密码。我该怎么办？

您无法找回密码。但是，您可以将其重置为其他内容。为此，您需要：

  • 编辑 pg_hba.conf，临时允许 trust 授权
  • 重新加载配置文件（pg_ctl reload）
  • 连接并发出 ALTER ROLE / PASSWORD 以设置新密码
  • 再次编辑 pg_hba.conf 并恢复之前的设置
  • 再次重新加载配置文件

PostgreSQL 有存储过程吗？

PostgreSQL 没有。但是，PostgreSQL 具有非常强大的函数和用户定义函数功能，可以完成其他 RDBMS 存储例程（过程和函数）可以完成的大多数事情，并且在许多情况下，还可以完成更多事情。

这些函数可以是不同的类型，并且可以用几种编程语言实现。（有关更多详细信息，请参阅文档。 用户定义函数）

PostgreSQL 函数可以通过多种方式调用。如果您想以在其他 RDBMS 中调用存储过程的方式调用函数（通常是一个有副作用的函数，但您不关心它的结果，例如因为它返回 void），一个选择是使用 PL/pgSQL 语言 作为您的过程和 PERFORM 命令。示例

PERFORM theNameOfTheFunction(arg1, arg2);

请注意，调用

SELECT theNameOfTheFunction(arg1, arg2);

即使函数返回 void 也会产生结果（此结果将是一行，包含一个 void 值）。

PERFORM 因此可用于丢弃此无用的结果。

与真正的存储过程相比，Pg 的存储函数的主要限制是：

  • 无法返回多个结果集
  • 不支持自治事务 (BEGIN、COMMIT 和 ROLLBACK 在函数内)
  • 不支持 SQL 标准 CALL 语法，尽管 ODBC 和 JDBC 驱动程序将为您翻译调用。

为什么 BEGIN、ROLLBACK 和 COMMIT 在存储过程/函数中不起作用？

PostgreSQL 不支持其存储函数中的自治事务。与所有 PostgreSQL 查询一样，存储函数始终在事务中运行，并且不能在事务之外运行。

如果您需要存储过程来管理事务，您可以查看 dblink 接口，或者从客户端脚本执行工作。在某些情况下，您可以使用 PL/PgSQL 中的异常块 完成所需的操作，因为每个 BEGIN/EXCEPTION/END 块都会创建一个子事务。

为什么 "SELECT count(*) FROM bigtable;" 很慢？

在 9.2 及更高版本中，通常情况下不会很慢，这得益于 索引仅扫描.

有关旧版本的信息，请参阅 慢速计数.

为什么我的查询在作为准备好的查询运行时速度慢得多？

在 PostgreSQL 9.2 及更高版本中，这种情况很少见，因为 PostgreSQL 可以决定在每次执行的基础上使用通用计划或值优化计划。计划者仍然可能做出错误的选择，因此以下内容仍然有些相关性

当 PostgreSQL 在规划阶段就已知晓完整查询和所有参数时，它可以利用表中的统计信息来判断查询中使用的值在某一列中是否非常常见或非常罕见。这可以让它改变数据获取方式，从而提高效率，因为它知道从查询的某一部分中会获得很多结果或很少的结果。例如，如果查询“active=y”，并且知道表中 99% 的记录都有“active=y”，那么它可能会选择顺序扫描而不是索引扫描，因为在这种情况下，顺序扫描会更快。

在预备查询中，PostgreSQL 在创建计划时没有所有参数的值。它必须尝试选择一个“安全”的计划，该计划应该在执行预备查询时无论提供什么参数值都能很好地工作。不幸的是，如果提供的参数值远比表中随机选择的某些值的平均值更常见或更罕见，那么这个计划可能不太合适。

如果您怀疑这个问题正在影响您，请先使用 EXPLAIN 命令比较慢速查询和快速查询。查看 EXPLAIN SELECT query... 的输出结果，并将其与 PREPARE query... ; EXPLAIN EXECUTE query... 的结果进行比较，看看计划是否明显不同。EXPLAIN ANALYZE 可能会提供更多信息，例如行数估计和计数。

通常，遇到这个问题的人是在尝试使用预备查询作为一种安全措施来防止 SQL 注入，而不是作为一种性能优化选项来处理频繁执行且具有各种不同参数的成本高昂的计划查询。这些人应该考虑使用客户端预备语句，如果他们的客户端界面 (如 PgJDBC) 支持的话。PostgreSQL 协议支持参数化查询，无需服务器端持久化预备语句，大多数客户端驱动程序通过其客户端预备语句接口支持使用此功能。

目前，PostgreSQL 还没有提供一种方法来请求使用特定参数值集重新规划预备语句；但是，9.2 及更高版本可能会在统计信息表明需要重新规划时自动执行此操作。

参见 Using_EXPLAIN。如果您要在邮件列表中寻求帮助，请阅读 Guide to reporting problems。

为什么我的查询在函数中运行比单独运行慢得多？

参见 FAQ#Why is my query much slower when run as a prepared query?。PL/PgSQL 函数中的查询是预备的并被缓存的，因此它们的执行方式与您自己PREPARE 然后 EXECUTE 查询的方式非常相似。

如果遇到非常严重的问题，即使改善表统计信息或调整查询也无济于事，您可以通过强制 PL/PgSQL 在每次执行时重新准备查询来解决此问题。为此，请在 PL/PgSQL 中使用 EXECUTE ... USING 语句将查询作为文本字符串提供。或者，可以使用 quote_literal 或 quote_nullable 函数来转义替换到查询文本中的参数。

为什么我的字符串排序不正确？

首先，请确保您正在使用您想要使用的区域设置。使用 SHOW lc_collate 来显示生效的数据库级区域设置。如果您使用的是按列区域设置，请检查这些区域设置。如果一切都是您想要的，那么请继续阅读。

PostgreSQL 使用 C 库的区域设置功能来排序字符串。因此，如果字符串的排序顺序与您预期的不符，则问题很可能出在 C 库中。您可以使用 sort 实用程序在文本文件上验证 C 库对排序的理解，例如：

LC_COLLATE=xx_YY.utf8 sort testfile.txt

如果这导致了与 PostgreSQL 给出的相同顺序，那么问题不在 PostgreSQL 内部。

PostgreSQL 与 libc 的行为不同，因为它通过按字节顺序排序字符串来打破平局。这在实践中很少会产生影响，通常不是用户抱怨排序顺序问题的原因，但它可能会影响某些情况，例如，混合使用组合和预组合 Unicode 字符。

如果问题出在 C 库中，您需要向您的操作系统维护人员反映这个问题。但是，请注意，虽然 C 库中已知存在区域设置定义中的实际错误，但 C 库更可能正确，而“正确”意味着它遵循某些公认的国际或国家标准。您可能期望对语言的排序规则进行多种同样有效的解释中的一种。

常见的抱怨模式包括

  • 空格和特殊字符：排序算法通常会进行多次遍历。首先，比较所有字母，忽略空格和标点符号。然后，比较空格和标点符号以打破平局。（这是对实际发生情况的简化。）除非更改区域设置定义本身（即使那样也很难），否则无法更改此行为。您可能希望稍微调整数据结构以避免这个问题。例如，如果您正在对姓名字段进行排序，您可以将该字段拆分为姓氏和名字字段，避免中间的空格。
  • 大小写：除 C 区域设置之外的其他区域设置通常会将大写字母和小写字母一起排序。因此，顺序将类似于 a A b B c C ... 而不是基于 ASCII 字节值的排序所产生的 A B C ... a b c ...。这是正确的。
  • 德语区域设置：ä 作为 a 或 ae 的排序顺序。这两种排序都是有效的（参见 http://de.wikipedia.org/wiki/Alphabetische_Sortierung），但大多数 C 库只提供第一种排序。要解决此问题，需要创建自定义区域设置。这是可能的，但需要一些工作。
  • 它不是按照 ASCII/字节顺序排序的。不，不是，它不应该按照 ASCII/字节顺序排序。ASCII 是一种编码，而不是排序顺序。如果您想要按照这种顺序排序，您可以使用 C 区域设置，但那样您就无法使用非 ASCII 字符。

也就是说，如果您使用的是 Mac OS X 或 BSD 系列操作系统，并且您使用的是 UTF-8，那么请放弃。这些操作系统上的区域设置定义已损坏。

为什么 PostgreSQL 在子查询中使用错误的名称时不会报告找不到列的错误？

   WITH tblA (id, foo_col) AS ( VALUES (1, 'A'), (2, 'B') ),
        tblB (bar_col)     AS ( VALUES ('B'),    ('C') )
 SELECT id
   FROM tblA
  WHERE foo_col IN (SELECT foo_col FROM tblB);

在上面的查询中，您将从 tblA 中选择两行，即使您希望只选择 id = 2 的行。当您最终发现 tblB 中没有 foo_col 时，您会抱怨 PostgreSQL 应该意识到这一点并报错。但是，根据 SQL 标准 - 并且出于良好的可用性原因 - 对子选择中 foo_col 的引用是在“外部引用”范围内找到的，tblA 存在于该范围内，并且该范围内确实有一个 foo_col 列。由于表达式 foo_col = foo_col 将始终计算为 true，因此 where 子句本身将始终返回 true - 只要 子查询 (在本例中为 tblB) 返回至少一行 (即，tblB WHERE false 将产生空结果)。

具体来说，上面的查询包含一个相关子查询 (wikipedia)。这意味着在逻辑上，对于每个源行都会评估一次子选择，并且在规划过程中对其执行进行参数化，然后在执行过程中，将父/外部查询中的特定行值注入。在 sane 和有意的方式中使用相关子查询超出了此 FAQ 条目的范围。

除了可读性问题之外，许多人会选择将表名追加到在子查询中使用的所有列引用，以避免此错误。在查询中指定 tblB.foo_col 将会引发预期的错误。

顺便说一句，大多数报告的这种形式的查询都是格式错误的半连接查询 (即，它们使用 IN 而不是 EXISTS)。编写正确的半连接查询会将列引用移到 where 子句中，在该子句中使用表引用更加自然。

   WITH tblA (id, foo_col) AS ( VALUES (1, 'A'), (2, 'B') ),
        tblB (bar_col)     AS ( VALUES ('B'),    ('C') )
 SELECT id
   FROM tblA
  WHERE EXISTS (SELECT 1 
                  FROM tblB 
                 WHERE tblB.bar_col = tblA.foo_col);

讨论 EXISTS 与 IN 的优缺点也超出了此 FAQ 条目的范围。

如何防止普通用户看到我的商业秘密？

您不能。系统目录不参与基于授予/撤销的权限系统；任何具有数据库登录权限的用户都可以查看系统目录的内容。

PostgreSQL 将所有永久创建的对象存储在系统目录表中，可以通过 pg_catalog 模式访问。所有用户登录数据库后，都可以查看 pg_catalog 中几乎所有表的内容 (直接或通过视图)。特别要注意的是 pg_proc (prosrc) 和 pg_views (definition) 以及 pg_description (description)。前两个包含系统的大部分存储的业务逻辑，而最后一个是用户可以在其中提供与任何对象相关的任意元数据的地方。

您可以通过依赖 C 语言函数和过程来获得一定程度的保密性，因为只有系统的 DBA 才能查看它们的源代码。您也可以利用外部数据包装器或 dblink。

数据建模问题

如何加密我的数据？

首先，参见 Bruce Momjian 的演示文稿，了解数据保护的一般主题。另请参见 [GENERAL] Re: Two-way encryption 作为起点。考虑是否需要类似于 自动加密类型 的东西。

摘自“https://wiki.postgresql.ac.cn/index.php?title=FAQ&oldid=38522”