PostgreSQL学习

PostgreSQL版本:16.6

下载地址: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

第 1 节. PG本地和Docker安装步骤

一、本地安装PostgreSQL

以下是在本地 Windows 系统上安装 PostgreSQL 16.6 的流程：

1. 下载安装包

• 访问 PostgreSQL 官方下载页面：PostgreSQL 下载
• 选择适合您系统的安装包（建议选择 PostgreSQL Server 和 pgAdmin 4）。

2. 运行安装程序

• 双击下载的安装程序文件，启动安装向导。
• 在安装向导中，选择安装组件，包括 PostgreSQL Server、pgAdmin 4 和 Command Line Tools 等。

3. 配置安装选项

• 选择安装目录：默认安装目录为 C:\Program Files\PostgreSQL\16，您可以根据需要更改。
• 设置数据目录：默认数据目录为 C:\Program Files\PostgreSQL\16\data，您可以根据需要更改。
• 设置超级用户密码：输入并确认超级用户 postgres 的密码。
• 设置端口号：默认端口号为 5432，您可以根据需要更改。
• 配置高级选项：如需要，可以配置其他高级选项，如 locale 和 encoding 等。

4. 完成安装

• 点击 Install 按钮开始安装。
• 安装完成后，您可以选择是否启动 Stack Builder 来安装其他工具和驱动程序。

5. 验证安装

• 打开 Dbeaver，连接到本地服务器，数据库:postgres ,默认端口:5432, 输入超级用户 postgres 的密码，验证连接是否成功。
• 打开命令行工具，输入 psql -U postgres -h localhost -p 5432，验证是否可以成功登录到数据库。
• PostgreSQL 默认的数据库名字是postgres， 默认的模式schema是:public，pg的模式相当于MySQL的数据库,可以创建不同的模式。

6. DBCP配置

########postgresql########
driverClassName=org.postgresql.Driver
url=jdbc:postgresql://localhost:5432/postgres?currentSchema=public&encoding=UTF-8&timezone=UTC
username=postgres
password=passw0rd

参考资料

• PostgreSQL 官方文档

• PostgreSQL 16 在 Windows 下安装

• Windows 10 上安装 PostgreSQL 16

• PostgreSQL 在 Windows 上的安装与配置

二、Docker安装PostgreSQL

以下是在192.168.184.120 机器上，使用 Docker 安装 PostgreSQL 16.6 的流程：

1. 拉取镜像

从 Docker Hub 上拉取 PostgreSQL 16.6 的镜像：

bash复制

docker pull swr.cn-north-4.myhuaweicloud.com/ddn-k8s/docker.io/postgres:16.6-alpine3.21
docker tag  swr.cn-north-4.myhuaweicloud.com/ddn-k8s/docker.io/postgres:16.6-alpine3.21  docker.io/postgres:16.6

2. 创建并运行 PostgreSQL 容器

使用以下命令创建并运行一个 PostgreSQL 容器，并配置数据持久化：

docker run --name postgres16 -e POSTGRES_PASSWORD=passw0rd -p 5432:5432 -v /opt/postgresql/data:/var/lib/postgresql/data -d docker.io/postgres:16.6

• --name postgres16：指定容器名称为 postgres16。
• -e POSTGRES_PASSWORD=mysecretpassword：设置 PostgreSQL 的超级用户密码。
• -p 5432:5432：将容器的 5432 端口映射到主机的 5432 端口。
• -v /opt/postgresql/data:/var/lib/postgresql/data：将主机的目录（/opt/postgresql/data）挂载到容器的数据目录（/var/lib/postgresql/data），以实现数据持久化。

3. 进入容器

如果需要进入容器内部，可以使用以下命令：

bash复制

docker exec -it postgres16 bash

5. 连接到数据库

在容器内部或宿主机上，可以使用 psql 命令连接到数据库：

bash复制

psql -U postgres -h localhost -p 5432

• -U postgres：指定用户名为 postgres。
• -h localhost：指定主机名为 localhost。
• -p 5432：指定端口号为 5432。

6. DBCP配置

已经在192.168.184.120安装了PG,测试可以直接使用。PG默认的模式是：public，可以通过设置currentSchema切换模式。

########postgresql########
driverClassName=org.postgresql.Driver
url=jdbc:postgresql://192.168.184.120:5432/postgres?currentSchema=public&encoding=UTF-8&timezone=UTC
username=postgres
password=passw0rd

参考资料

• Docker 安装 PostgreSQL 16.6
• Docker+PostgreSQL数据库
• Docker 容器化部署 PostgreSQL

PostgreSQL 基础教程

https://www.rockdata.net/zh-cn/tutorial/toc/

本 PostgreSQL 教程可帮助您快速了解 PostgreSQL。您将通过许多实际示例快速掌握 PostgreSQL，并将这些知识应用于使用 PostgreSQL 开发应用程序。

如果你是 …

• 寻求快速学习 PostgreSQL。
• 使用 PostgreSQL 作为后端数据库管理系统开发应用程序。
• 从其他数据库管理系统（例如 MySQL、Oracle 和 Microsoft SQL Server）迁移到 PostgreSQL。

您将在此网站上找到快速有效地开始使用 PostgreSQL 所需的所有信息。

PostgreSQL 教程演示了 PostgreSQL 的许多独特功能，这些功能使其成为最先进的开源数据库管理系统。

PostgreSQL 入门

本部分向您展示如何在 Windows、Linux 和 macOS 上安装 PostgreSQL，帮助您开始使用 PostgreSQL。您还将学习如何使用 psql 工具连接到 PostgreSQL，以及如何将示例数据库加载到 PostgreSQL 中进行练习。

PostgreSQL 基础教程

首先，您将学习如何使用基本数据查询技术从单个表中查询数据，包括查询数据、对结果集进行排序和过滤行。然后，您将了解高级查询，例如连接多个表、使用集合操作以及构造子查询。最后，您将学习如何管理数据库表，例如创建新表或修改现有表的结构。

第 1 节. 查询数据

• 简单查询 – 向您展示如何从单个表中查询数据。
• 列别名 – 了解如何为查询中的列或表达式分配临时名称。
• 排序 – 指导您如何对查询返回的结果集进行排序。
• 去重查询 – 为您提供一个删除结果集中重复行的子句。
• 分页查询 – 指导您如何对查询返回的结果集进行分页。

第 2 节. 过滤数据

• WHERE – 根据指定条件过滤行。
• LIMIT – 获取查询生成的行的子集。
• FETCH – 限制查询返回的行数。
• IN – 选择与值列表中的任何值匹配的数据。
• BETWEEN – 选择值范围内的数据。
• LIKE – 基于模式匹配过滤数据。
• IS NULL – 检查值是否为空。

第 3 节. 连接多个表

• 连接 – 向您展示 PostgreSQL 中连接的简要概述。
• 表别名 – 描述如何在查询中使用表别名。
• 内连接 – 从一个表中选择在其他表中具有相应行的行。
• 左连接 – 从一个表中选择行，这些行在其他表中可能有也可能没有对应的行。
• 自连接 – 通过将表与自身进行比较来将表与其自身连接。
• 完全外连接 – 使用完全连接查找一个表中在另一个表中没有匹配行的行。
• 交叉连接 – 生成两个或多个表中的行的笛卡尔积。
• 自然连接 – 根据连接表中的公共列名称，使用隐式连接条件连接两个或多个表。
• 横向连接 – 通过连接交叉引用子查询中的行，和构建复合结果集。

第 4 节. 数据分组

• GROUP BY – 将行分成组并对每个组应用聚合函数。
• HAVING – 对组应用条件。
• PARTITION BY – 将行分成组并对每个组应用窗口函数。

第 5 节. 集合运算

• UNION – 将多个查询的结果集合并为一个结果集。
• INTERSECT – 组合两个或多个查询的结果集并返回一个结果集，该结果集的行都出现在两个结果集中。
• EXCEPT – 返回第一个查询中未出现在第二个查询的输出中的行。

第 6 节. 分组集、多维分组和汇总

• 分组集 – 在报告中生成多个分组集。
• CUBE – 定义多个分组集，其中包括所有可能的维度组合。
• ROLLUP – 生成包含总计和小计的报告。

第 7 节. 子查询

• 子查询 – 编写一个嵌套在另一个查询中的查询。
• 相关子查询 – 向您展示如何使用相关子查询，来执行依赖于正在处理的当前行的值的查询。
• ANY – 通过将某个值与子查询返回的一组值进行比较来检索数据。
• ALL – 通过将值与子查询返回的值列表进行比较来查询数据。
• EXISTS – 检查子查询返回的行是否存在。

第 8 节. 公共表表达式

• PostgreSQL CTE – 向您介绍 PostgreSQL 公共表表达式或 CTE。
• 使用 CTE 的递归查询 – 讨论递归查询并学习如何在各种上下文中应用它。

第 9 节. 修改数据

在本节中，您将学习如何使用INSERT语句向表中插入数据、使用UPDATE语句修改现有数据以及使用DELETE语句删除数据。此外，您还将学习如何使用 UPSERT 语句来合并数据。

• 插入 – 指导您如何将单行插入表中。
• 插入多行 – 向您展示如何在表中插入多行。
• 更新 – 更新表中的现有数据。
• 连接更新 – 根据另一个表中的值更新表中的值。
• 删除 – 删除表中的数据。
• 连接删除 – 根据另一个表中的值删除表中的行。
• 级联删除 – 在删除父表中的行时级联删除子表中的相关行。
• UPSERT – 如果新行已存在于表中，则插入或更新数据。
• 合并 – 有条件地插入、更新和删除一个表中的行。

第 10 节. 事务

• PostgreSQL 事务 – 向您展示如何使用BEGIN、COMMIT和ROLLBACK语句，来处理 PostgreSQL 中的事务。
• PostgreSQL 子事务 – 向您展示如何使用SAVEPOINT、ROLLBACK TO SAVEPOINT和RELEASE SAVEPOINT语句，来处理 PostgreSQL 子事务。
• 并发锁定和组合事务 – 向您介绍什么是组合事务，它为什么会存在，以及会在什么情况下出现。
• SKIP LOCKED 跳过锁定 – 向您介绍如何在 PostgreSQL 中使用SKIP LOCKED，以避免和解决死锁的问题。

第 11 节. 导入和导出数据

您将学习如何使用COPY命令、DBeaver 工具，以 CSV、SQL 文件格式对 PostgreSQL 数据进行导入和导出。

• 将 CSV 文件导入表中 – 向您展示如何将 CSV 文件导入表中。
• 导出表到 CSV 文件 – 向您展示如何将表导出到 CSV 文件。
• 使用 DBeaver 导入数据 – 向您展示如何使用 DBeaver 将数据从文件导入到表中。
• 使用 DBeaver 导出表 – 向您展示如何使用 DBeaver 将表导出到不同类型和格式的文件。
• 使用 DBeaver 合并数据 – 向您展示如何使用 DBeaver 将文件中的数据合并到表中。

第 12 节. 管理表

在本节中，您将开始探索 PostgreSQL 数据类型，并向您展示如何创建新表和修改现有表的结构。

• 数据类型 – 涵盖最常用的 PostgreSQL 数据类型。
• 创建表 – 指导您如何在数据库中创建新表。
• SELECT INTO 和 CREATE TABLE AS – 向您展示如何从查询的结果集创建新表。
• 使用 SERIAL 自增列 – 使用 SERIAL 将自动增量列添加到表中。
• 序列 – 向您介绍序列并描述如何使用序列生成数字序列。
• 标识列 – 向您展示如何使用标识列。
• 生成列 – 向您展示如何使用生成列。
• 更改表 – 修改现有表的结构。
• 重命名表 – 将表的名称更改为新名称。
• 添加列 – 向您展示如何向现有表添加一列或多列。
• 删除列 – 演示如何删除表的列。
• 更改列数据类型 – 向您展示如何更改列的数据。
• 重命名列 – 说明如何重命名表中的一列或多列。
• 删除表 – 删除现有表及其所有依赖对象。
• 截断表 – 快速有效地删除大表中的所有数据。
• 临时表 – 向您展示如何使用临时表。
• 复制表 – 向您展示如何将表格复制到新表格。
• 表分区 – 向您展示如何使用表分区。
• 管理分区表 – 向您展示如何管理分区表。
• 引发表重写的 DDL 命令 – 向您介绍哪些 DDL 命令会导致表的重写。
• 以最短的停机时间更改列 – 向您介绍如何以最短的停机时间更改列。

第 13 节. 了解 PostgreSQL 约束

• 主键 – 说明在创建表或向现有表添加主键时如何定义主键。
• 外键 – 展示如何在创建新表时定义外键约束或为现有表添加外键约束。
• 检查约束 – 添加逻辑以基于布尔表达式检查值。
• 唯一约束 – 确保一列或一组列中的值在整个表中是唯一的。
• 非空约束 – 确保列中的值不是NULL。

第 14 节. 深入了解 PostgreSQL 数据类型

• 布尔型 – 使用布尔数据类型存储TRUE和FALSE值。
• 字符型 – 了解如何使用各种字符类型，包括char、varchar和text。
• numeric – 向您展示如何使用numeric类型来存储需要精度的值。
• double precision – 了解如何在数据库中存储不准确的可变精度数字。double precision 类型也称为 float 类型。
• real – 指导您如何在数据库中使用单精度浮点数。
• 整型 – 向您介绍 PostgreSQL 中的各种整数类型，包括smallint、int和bigint。
• date – 引入date用于存储日期值的数据类型。
• 时间戳 – 快速了解时间戳数据类型。
• 间隔 – 向您展示如何使用间隔数据类型有效地处理一段时间。
• time – 使用time数据类型来管理一天中的时间值。
• UUID – 指导您如何使用UUID数据类型以及如何使用提供的模块生成UUID值。
• 数组 – 向您展示如何使用数组，并向您介绍一些用于数组操作的方便函数。
• hstore – 向您介绍数据类型，它是存储在 PostgreSQL 中单个值中的一组键/值对。
• JSON – 说明如何使用 JSON 数据类型，并向您展示如何使用一些最重要的 JSON 运算符和函数。
• 用户定义的数据类型 – 向您展示如何使用CREATE DOMAIN和CREATE TYPE语句创建用户定义的数据类型。
• bytea – 了解如何在数据库中存储二进制字节串。

第 15 节. 条件表达式和运算符

• CASE – 向您展示如何使用CASE表达式构成条件查询。
• COALESCE – 返回第一个非空参数。您可以使用它将NULL替换为一个默认值。
• NULLIF – 如果第一个参数等于第二个参数则返回NULL。
• CAST – 从一种数据类型转换为另一种数据类型，例如，从字符串转换为整数，从字符串转换为日期。

第 16 节. PostgreSQL 实用程序

• psql 命令 – 向您展示最常见的 psql 命令，帮助您更快、更有效地与 psql 交互。

第 17 节. 故障处理

• 应对死锁 – 指导您如何在 PostgreSQL 中处理死锁的问题。
• 处理数据块损坏 – 指导您如何处理 PostgreSQL 中损坏的数据块。
• 处理 TOAST 数据损坏 – 指导您如何处理 PostgreSQL 中损坏的 TOAST 数据。
• 处理统计信息损坏 – 指导您如何处理 PostgreSQL 中的统计信息损坏。
• 处理 PL/pgSQL 运行时错误 – 指导您如何对 PostgreSQL 中的 PL/pgSQL 运行时错误进行处理。
• 页面缓存如何影响查询性能？ – 指导您如何分析页面缓存对 PostgreSQL 性能的影响。
• 处理事务 ID 回卷的故障 – 指导您如何处理 PostgreSQL 中事务 ID 回卷的故障。
• 检查后端进程的内存使用情况 – 指导您如何检查 PostgreSQL 中后端进程的内存使用情况，和进行故障处理。
• 记录函数内错误的调用栈 – 指导您如何记录在指定函数内发生错误时的调用栈。
• 分区剪枝不起作用的原因 – 向您介绍在 PostgreSQL 中分区剪枝不起作用的主要原因。
• 处理 pg_wal 目录中的 WAL 积压 – 指导您如何在 PostgreSQL 中排查 pg_wal 目录中的 WAL 积压问题。
• 处理服务器可用内存不足的问题 – 指导您如何在 PostgreSQL 服务器上处理可用内存不足的问题。
• 处理缓存命中率低的问题 – 指导您如何在 PostgreSQL 中处理缓存命中率低的问题。

第 18 节. PostgreSQL 技巧

• 如何比较两个表 – 描述如何比较数据库中两个表中的数据。
• 使用 pgAdmin 4 对比数据架构 – 指导您如何使用 pgAdmin 4 提供的架构差异功能，比较两个数据库或两个模式之间的对象。
• 使用 DBeaver 迁移表数据 – 向您介绍使用 DBeaver 工具，在不同数据库之间或同一数据库内的表之间传输数据。
• 如何在 PostgreSQL 中删除重复行 – 向您展示从表中删除重复行的各种方法。
• 如何生成某个范围内的随机数 – 说明如何生成特定范围内的随机数。
• 查询 JSON 列中内嵌的数组 – 描述如何在 PostgreSQL 中查询 JSON 列中内嵌的数组。
• 更改 JSON 列中内嵌的数组 – 描述如何在 PostgreSQL 中修改 JSON 列中内嵌的数组。
• PostGIS 基础用法 – 向您介绍 PostGIS 的一些基础用法。
• 使用 PostGIS 进行基础的地理空间数据查询 – 向您演示用于处理地理空间数据的基础 PostGIS 查询。

PostgreSQL 高级教程

这个 PostgreSQL 高级教程涵盖了高级概念，包括存储过程、索引、视图、触发器和数据库管理。

PostgreSQL 函数

PostgreSQL 为内置数据类型提供了大量的函数。本节向您展示如何使用一些最常用的 PostgreSQL 函数。

PostgreSQL PL/pgSQL

此 PostgreSQL 存储过程部分将逐步向您展示如何使用 PL/pgSQL 过程语言开发 PostgreSQL 用户定义函数。

PostgreSQL 触发器

本节向您介绍 PostgreSQL 触发器概念，并展示如何在 PostgreSQL 中管理触发器。

PostgreSQL 视图

我们将向您介绍数据库视图概念，并向您展示如何管理视图，例如在数据库中创建、更改和删除视图。

PostgreSQL 索引

PostgreSQL 索引是增强数据库性能的有效工具。索引可以帮助数据库服务器比没有索引时更快地找到特定行。

PostgreSQL 优化

本节向您介绍 PostgreSQL 性能优化，并展示如何在 PostgreSQL 中优化各种场景的性能问题。

PostgreSQL 管理

PostgreSQL 管理涵盖 PostgreSQL 数据库服务器最重要的活动，包括角色和数据库管理、备份和恢复。

PostgreSQL 监控

PostgreSQL 监控涵盖 PostgreSQL 数据库服务器最重要的监控和运维活动。

应用程序编程接口

本节向您展示，如何从使用流行编程语言（例如 Java、Python 和 PHP）的应用程序，与 PostgreSQL 数据库进行交互。

• PostgreSQL Java 教程 – 此 PostgreSQL JDBC 部分向您展示，如何使用 Java JDBC 驱动程序与 PostgreSQL 数据库进行交互。
• PostgreSQL Python 教程 – 此 PostgreSQL Python 部分向您展示，如何使用 Python 编程语言与 PostgreSQL 数据库进行交互。
• 使用 Golang 连接到 PostgreSQL – 向您介绍如何使用 Go 编程语言与 PostgreSQL 数据库进行交互。

语法差异

如果列别名包含一个或多个空格，则需要用双引号将其引起来，如下所示：

包含空格的列别名

如果列别名包含一个或多个空格，则需要用双引号将其引起来，如下所示：

column_name AS "column alias"

例如：

SELECT
    first_name || ' ' || last_name "full name"
FROM
    customer;

PostgreSQL SELECT DISTINCT 子句简介

在这种情况下，column1和column2列中的值的组合将用于计算重复项。

PostgreSQL 还提供了DISTINCT ON (expression)来保留每组重复项的第一行的功能，使用以下语法：

SELECT
   DISTINCT ON (column1) column_alias,
   column2
FROM
   table_name
ORDER BY
   column1,
   column2;

从SELECT语句返回的行的顺序是未指定的，因此每组重复项的第一行也是未指定的。

最好始终使用带有DISTINCT ON(expression)的 ORDER BY 子句，以使结果集可预测。

请注意，DISTINCT ON表达式必须与ORDER BY子句中最左边的表达式匹配。

PostgreSQL ORDER BY 子句和 NULL

在数据库世界中，NULL是一个标记，指示丢失的数据或数据在记录时未知。

对包含NULL的行进行排序时，可以使用ORDER BY子句的NULLS FIRST或NULLS LAST选项，指定NULL与其他非空值的顺序：

ORDER BY sort_expresssion [ASC | DESC] [NULLS FIRST | NULLS LAST]

在此示例中，ORDER BY子句按升序对sort_demo表的num列中的值进行排序。它将NULL置于其他值之后。

因此，如果您使用ASC选项，ORDER BY子句默认使用NULLS LAST选项。因此，以下查询返回相同的结果：

SELECT num
FROM sort_demo
ORDER BY num NULLS LAST;

要放置NULL在其他非空值之前，可以使用NULLS FIRST选项：

SELECT num
FROM sort_demo
ORDER BY num NULLS FIRST;

PostgreSQL 的分页和过滤技术

PostgreSQL 提供了多种方法来实现分页和过滤功能。其中，键集分页方法是在处理大型表时的一个非常有效的方法。

https://www.rockdata.net/zh-cn/tutorial/dml-paginate/

键集分页方法

键集分页方法基于结果集中的唯一键（通常是主键）进行分页。它通过保存上一次查询的最后一行的键值，并将其作为下一次查询的起点来实现分页。

以下是一个使用键集分页方法的示例查询：

SELECT *
FROM large_table
WHERE id > last_key
ORDER BY id
LIMIT 10;

在此示例中，我们使用唯一键 id 进行分页。上一次查询的最后一条数据的 id 值将用于下一次查询的过滤条件。通过以此方式逐步递增分页查询，我们可以避免加载整个结果集，从而提高性能和效率。

OFFSET 和 LIMIT 分页

除了键集分页方法外，PostgreSQL 还提供了使用 OFFSET 和 LIMIT 子句进行分页的方式。OFFSET 子句用于指定开始返回结果的位置，LIMIT 子句用于指定返回结果的数量。

以下是一个使用 OFFSET 和 LIMIT 分页的示例查询：

SELECT *
FROM large_table
ORDER BY id
OFFSET 1000
LIMIT 10;

PostgreSQL LIMIT 子句简介

https://www.rockdata.net/zh-cn/tutorial/dml-limit/

PostgreSQL 的LIMIT是 SELECT 语句的可选子句，用于限制查询返回的行数。

下面说明了LIMIT子句的语法：

SELECT select_list 
FROM table_name
ORDER BY sort_expression
LIMIT row_count

该语句返回查询生成的row_count行。如果row_count为零，则查询返回空集。如果row_count是NULL，查询将返回与没有LIMIT子句相同的结果集。

如果您想在返回row_count行之前跳过一些行，请在LIMIT子句之后放置OFFSET子句，如下所示：

SELECT select_list
FROM table_name
LIMIT row_count OFFSET row_to_skip;

该语句首先跳过row_to_skip行，然后返回查询生成的row_count行。如果row_to_skip为零，则该语句将像没有OFFSET子句一样工作。

由于表可能以未指定的顺序存储行，因此当您使用LIMIT子句时，应始终使用 ORDER BY 子句来控制行顺序。如果不使用ORDER BY子句，则可能会得到具有未指定行顺序的结果集。

PostgreSQL FETCH 子句简介

为了限制查询返回的行数，您经常使用LIMIT子句。LIMIT子句被许多关系数据库管理系统广泛使用，例如 MySQL、H2 和 HSQLDB。但是，LIMIT子句不是 SQL 标准。

为了符合 SQL 标准，PostgreSQL 支持FETCH子句来检索查询返回的行数。

请注意，FETCH子句是在 SQL:2008 中作为 SQL 标准的一部分引入的。

下面说明了 PostgreSQL 的FETCH子句的语法：

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

FETCH 对比 LIMIT

FETCH子句在功能上等同于LIMIT子句。如果您计划使您的应用程序与其他数据库系统兼容，则应该使用FETCH子句，因为它遵循 SQL 标准。

PostgreSQL WHERE 子句概述

https://www.rockdata.net/zh-cn/tutorial/dml-where/

PostgreSQL 的WHERE子句的语法如下：

SELECT select_list
FROM table_name
WHERE condition
ORDER BY sort_expression

WHERE子句出现在SELECT语句的FROM子句之后。WHERE子句使用condition来过滤从SELECT列表子句返回的行。

condition的计算结果必须为真、假或未知。它可以是布尔表达式或使用AND和OR运算符的布尔表达式的组合。

该查询仅返回满足WHERE子句中condition的行。换句话说，只有导致condition计算结果为 true 的行才会包含在结果集中。

PostgreSQL 计算WHERE子句的时间点，在FROM子句之后，在SELECT列表和ORDER BY子句之前：

如果在SELECT列表子句中使用列别名，则不能在WHERE子句中使用它们。

除了SELECT语句之外，您还可以使用UPDATE和DELETE语句中的WHERE子句来指定要更新或删除的行。

要构成WHERE子句中的条件，请使用比较运算符和逻辑运算符：

运算符	描述
=	等于
>	大于
<	小于
>=	大等于
<=	小等于
<> 或 !=	不等于
AND	逻辑运算符 AND
OR	逻辑运算符 OR
IN	如果值与列表中的任何值匹配，则返回 true
BETWEEN	如果值介于某个值范围之间，则返回 true
LIKE	如果值与模式匹配则返回 true
IS NULL	如果值为 NULL，则返回 true
NOT	对其他运算符的结果求反

7) 使用带有不等于运算符 (<>) 的 WHERE 子句示例

此示例查找名字以Bra开头且姓氏不是Motley的客户：

SELECT 
	first_name, 
	last_name
FROM 
	customer 
WHERE 
	first_name LIKE 'Bra%' AND 
	last_name <> 'Motley';

PostgreSQL BETWEEN 运算符简介

您可以使用BETWEEN运算符将一个值与一系列值进行匹配。下面说明了BETWEEN运算符的语法：

value BETWEEN low AND high;

如果value大等于low值且小等于high值，则表达式返回 true，否则返回 false。

您可以使用大等于 (>=) 或小等于 (<=) 运算符重写BETWEEN运算符，如下所示：

value >= low and value <= high

如果要检查值是否超出范围，可以将NOT运算符与BETWEEN运算符组合起来，如下所示：

value NOT BETWEEN low AND high;

以下表达式等效于使用NOT和BETWEEN运算符的表达式：

value < low OR value > high

如果要检查日期范围内的值，则应使用 ISO 8601 格式的文字日期，即 YYYY-MM-DD。例如，要获取付款日期在2007-02-07和2007-02-15之间的付款，请使用以下查询：

SELECT
	customer_id,
	payment_id,
	amount,
    payment_date
FROM
	payment
WHERE
	payment_date BETWEEN '2007-02-07' AND '2007-02-15';

PostgreSQL LIKE 运算符简介

假设您想要找到一位客户，但您不记得她的确切名字。但是，您可以记得她的名字以类似Jen开头。

如何从数据库中找到准确的客户？您可以通过查看名字列来查找customer表中的客户，看看是否有任何以Jen开头的值。但是，如果客户表包含大量行，则此过程可能会非常耗时。

幸运的是，您可以使用 PostgreSQL 的LIKE运算符，通过以下查询将客户的名字与字符串进行匹配：

SELECT
	first_name,
    last_name
FROM
	customer
WHERE
	first_name LIKE 'Jen%';

请注意，其中WHERE子句包含一个特殊表达式：first_name、LIKE运算符和包含百分号 (%) 的字符串。字符串'Jen%'称为模式。

该查询返回first_name列值以Jen开头且后跟任意字符序列的行。这种技术称为模式匹配。

您可以通过将文字值与通配符组合来构造模式，并使用LIKE或NOT LIKE运算符来查找匹配项。PostgreSQL 为您提供了两个通配符：

• 百分号 (%) 匹配任何零个或多个字符的序列。
• 下划线符号 (_) 匹配任何单个字符。

PostgreSQL LIKE运算符的语法如下：

value LIKE pattern

如果value与pattern匹配，则表达式返回 true。

要否定LIKE运算符，请按如下方式使用NOT运算符：

value NOT LIKE pattern

当value与pattern不匹配时，NOT LIKE运算符返回 true。

如果模式不包含任何通配符，则LIKE运算符的行为类似于等于 (=) 运算符。

PostgreSQL 对 LIKE 运算符的扩展

PostgreSQL 支持类似于LIKE运算符的ILIKE运算符。此外，ILIKE运算符匹配值时不区分大小写。例如：

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name ILIKE 'BAR%';

模式BAR%匹配以BAR、Bar、BaR等开头的任何字符串。如果您改用LIKE运算符，查询将不会返回任何行。

PostgreSQL 还提供了一些类似于LIKE, NOT LIKE, ILIKE和NOT ILIKE的运算符，如下所示：

运算符	等价于
~~	LIKE
~~*	ILIKE
!~~	NOT LIKE
!~~*	NOT ILIKE

在本教程中，您学习了如何使用 PostgreSQL 的LIKE和ILIKE运算符，通过模式匹配来查询数据。

NULL 和 IS NULL 运算符简介

在数据库世界中，NULL 意味着缺少信息或不适用。NULL 不是一个值，因此，您不能将它与任何其他值（例如数字或字符串）进行比较。NULL 与值的比较将始终得到 NULL，这意味着结果未知。

此外，NULL 不等于 NULL，因此以下表达式返回 NULL：

NULL = NULL

假设您有一个contacts表存储联系人的名字、姓氏、电子邮件和电话号码。在记录联系人时，您可能不知道联系人的电话号码。

为了解决这个问题，您可以将phone列定义为可为空列，并在保存联系人信息时将 NULL 插入到phone列中。

CREATE TABLE contacts(
    id INT GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(15),
    PRIMARY KEY (id)
);

因此，要获取电话列中没有存储任何电话号码的联系人，请使用以下语句：

SELECT
    id,
    first_name,
    last_name,
    email,
    phone
FROM
    contacts
WHERE
    phone IS NULL;

IS NOT NULL 运算符

要检查值是否不为 NULL，请使用IS NOT NULL运算符：

value IS NOT NULL

如果值不为 NULL，则表达式返回 true；如果值为 NULL，则表达式返回 false。

例如，要查找有电话号码的联系人，您可以使用以下语句：

SELECT
    id,
    first_name,
    last_name,
    email,
    phone
FROM
    contacts
WHERE
    phone IS NOT NULL;

3) 使用 DELETE 从表中删除多行

以下语句从links表中删除两行并返回已删除行的id列中的值：

DELETE FROM links
WHERE id IN (6,5)
RETURNING *;

输出：

4) 使用 DELETE 删除表中的所有行

以下语句使用不带WHERE子句的DELETE语句删除links表中的所有行：

DELETE FROM links;

现在links表是空的。

PostgreSQL upsert 简介

在关系数据库中，术语 upsert 称为合并。这个想法是，当您向表中插入新行时，如果该行已存在，PostgreSQL 将更新该行，否则，它将插入新行。这就是为什么我们称该操作为 upsert（更新或插入的组合）。

要在 PostgreSQL 中使用 upsert 功能，请使用INSERT ON CONFLICT语句，如下：

INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;

PostgreSQL 在INSERT语句中添加了ON CONFLICT target action子句以支持 upsert 功能。

在此语句中，target可以是以下之一：

• (column_name) – 列名称。
• ON CONSTRAINT constraint_name – 其中约束名称可以是 UNIQUE 约束的名称。
• WHERE predicate –带有谓词的WHERE 子句。

action可以是以下之一：

• DO NOTHING – 表示如果该行已存在于表中，则不执行任何操作。
• DO UPDATE SET column_1 = value_1, .. WHERE condition – 更新表中的一些字段。

请注意，ON CONFLICT子句仅在 PostgreSQL 9.5 和以上版本可用。如果您使用的是早期版本，则需要一种解决方法才能拥有更新插入功能。

如果你也在使用 MySQL，你会发现 upsert 功能与 MySQL 中的insert on duplicate key update语句类似。

以下语句与上面的语句等效，但它使用name列而不是唯一约束名称作为INSERT语句的目标。

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT (name) 
DO NOTHING;

假设，您想在插入已存在的客户时将新电子邮件与旧电子邮件连接起来，在这种情况下，您使用UPDATE子句作为INSERT语句的操作，如下所示：

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com') 
ON CONFLICT (name) 
DO 
   UPDATE SET email = EXCLUDED.email || ';' || customers.email;

子事务简介

在专业的应用程序中，很难在不遇到任何错误的情况下编写相当长的事务。为了解决这个问题，用户可以使用一种叫做 SAVEPOINT 的东西。顾名思义，保存点是事务中的一个安全位置，如果出现严重错误，应用程序可以返回到该位置。

保存点是一个数据库特性，它允许您在事务中创建命名点，以后可以回滚到该命名点，同时保持事务的其余部分不变。当您想要处理事务中的错误或异常，并有选择地回滚到事务中的特定点，而不必撤消到目前为止所做的所有更改时，保存点非常有用。

怎么工作的

以下是在 PostgreSQL 中使用保存点的方法：

使用BEGIN语句启动一个事务：

BEGIN;

这将开始一个新的事务。

在事务中，您可以使用SAVEPOINT语句创建一个保存点，并指定一个名称：

SAVEPOINT my_savepoint;

在此示例中，在事务中创建了一个名为my_savepoint的保存点。

在事务中执行一个或多个 SQL 操作，例如INSERT、UPDATE、DELETE等。

在任何时候，如果需要回滚到保存点，可以使用ROLLBACK TO语句：

ROLLBACK TO my_savepoint;

这将撤消在创建my_savepoint保存点后所做的所有更改，从而有效地将事务还原到该点。

您还可以使用RELEASE语句释放保存点：

RELEASE my_savepoint;

这将删除保存点，并允许事务从当前位置继续。

最后，当您准备好提交事务中所做的所有更改时，可以使用COMMIT语句：

COMMIT;

这会将事务中所做的所有更改保存到数据库中。

示例

下面是一个完整的示例：

CREATE TABLE test0 AS SELECT 1 AS i;
-- Start a main transaction
BEGIN;
-- Perform some operations within the transaction
UPDATE test0 SET i = i + 1;
-- Start a subtransaction
SAVEPOINT s1;
-- Continue with more operations
UPDATE test0 SET i = i - 1000;
-- Check the content in the table
SELECT * FROM test0;
  i
------
 -998
(1 row)
-- Something went wrong, let's roll back to the savepoint
ROLLBACK TO SAVEPOINT s1;
-- Continue with other operations
UPDATE test0 SET i = i + 1;
-- Finally, when everything is fine, commit the transaction
COMMIT;
-- Check the content in the table again
SELECT * FROM test0;
 i
---
 3
(1 row)

13.3. 显式锁定

• 13.3.1. 表级锁
• 13.3.2. 行级锁
• 13.3.3. 页级锁
• 13.3.4. 死锁
• 13.3.5. 咨询锁

PostgreSQL提供了多种锁模式用于控制对表中数据的并发访问。 这些模式可以用于在MVCC无法给出期望行为的情境中由应用控制的锁。 同样，大多数PostgreSQL命令会自动要求恰当的锁以保证被引用的表在命令的执行过程中 不会以一种不兼容的方式删除或修改（例如，TRUNCATE无法安全地与同一表中上的其他操作并发地执行，因此它在表上获得一个ACCESS EXCLUSIVE 锁来强制这种行为）。

要检查在一个数据库服务器中当前未解除的锁列表，可以使用pg_locks系统视图。 有关监控锁管理器子系统状态的更多信息，请参考第 28 章。

13.3.1. 表级锁

下面的列表显示了可用的锁模式和PostgreSQL自动使用它们的场合。 你也可以用LOCK命令显式获得这些锁。请记住所有这些锁模式都是表级锁，即使它们的名字包含“row”单词（这些名称是历史遗产）。 在一定程度上，这些名字反应了每种锁模式的典型用法 — 但是语意却都是一样的。 两种锁模式之间真正的区别是它们有着不同的冲突锁模式集合（参考表 13.2）。 两个事务在同一时刻不能在同一个表上持有属于相互冲突模式的锁（但是，一个事务决不会和自身冲突。例如，它可以在同一个表上获得ACCESS EXCLUSIVE锁然后接着获取ACCESS SHARE锁）。非冲突锁模式可以由许多事务同时持有。 请特别注意有些锁模式是自冲突的（例如，在一个时刻ACCESS EXCLUSIVE锁不能被多于一个事务持有)而其他锁模式不是自冲突的（例如，ACCESS SHARE锁可以被多个事务持有)。

表级锁模式

• ACCESS SHARE

  只与ACCESS EXCLUSIVE锁模式冲突。SELECT命令在被引用的表上获得一个这种模式的锁。通常，任何只*读取*表而不修改它的查询都将获得这种锁模式。

• ROW SHARE

  与EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。SELECT FOR UPDATE和SELECT FOR SHARE命令在目标表上取得一个这种模式的锁 （加上在被引用但没有选择FOR UPDATE/FOR SHARE的任何其他表上的ACCESS SHARE锁）。

• ROW EXCLUSIVE

  与SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。命令UPDATE、DELETE和INSERT在目标表上取得这种锁模式（加上在任何其他被引用表上的ACCESS SHARE锁）。通常，这种锁模式将被任何*修改表中数据*的命令取得。

• SHARE UPDATE EXCLUSIVE

  与SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表不受并发模式改变和VACUUM运行的影响。由VACUUM（不带FULL）、ANALYZE、 CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY、 CREATE STATISTICS以及某些ALTER INDEX 和 ALTER TABLE的变体获得(详细内容请参考这些命令的文档)。

• SHARE

  与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表不受并发数据改变的影响。由CREATE INDEX（不带CONCURRENTLY）取得。

• SHARE ROW EXCLUSIVE

  与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表不受并发数据修改所影响，并且是自排他的，这样在一个时刻只能有一个会话持有它。由CREATE TRIGGER和某些形式的 ALTER TABLE所获得。

• EXCLUSIVE

  与ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式只允许并发的ACCESS SHARE锁，即只有来自于表的读操作可以与一个持有该锁模式的事务并行处理。由REFRESH MATERIALIZED VIEW CONCURRENTLY获得。

• ACCESS EXCLUSIVE

  与所有模式的锁冲突（ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE）。这种模式保证持有者是访问该表的唯一事务。由ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW（不带CONCURRENTLY）命令获取。 很多形式的ALTER INDEX和ALTER TABLE也在这个层面上获得锁（见ALTER TABLE）。这也是未显式指定模式的LOCK TABLE命令的默认锁模式。

提示

只有一个ACCESS EXCLUSIVE锁阻塞一个SELECT（不带FOR UPDATE/SHARE）语句。

一旦被获取，一个锁通常将被持有直到事务结束。 但是如果在建立保存点之后才获得锁，那么在回滚到这个保存点的时候将立即释放该锁。 这与ROLLBACK取消保存点之后所有的影响的原则保持一致。 同样的原则也适用于在PL/pgSQL异常块中获得的锁：一个跳出块的错误将释放在块中获得的锁。

表 13.2. 冲突的锁模式

请求的锁模式	已存在的锁模式
ACCESS SHARE	ROW SHARE	ROW EXCL.	SHARE UPDATE EXCL.	SHARE	SHARE ROW EXCL.	EXCL.	ACCESS EXCL.
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCL.					X	X	X	X
SHARE UPDATE EXCL.				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCL.			X	X	X	X	X	X
EXCL.		X	X	X	X	X	X	X
ACCESS EXCL.	X	X	X	X	X	X	X	X

13.3.2. 行级锁

除了表级锁以外，还有行级锁，在下文列出了行级锁以及在哪些情境下PostgreSQL会自动使用它们。 行级锁的完整冲突表请见表 13.3。注意一个事务可能会在相同的行上保持冲突的锁，甚至是在不同的子事务中。 但是除此之外，两个事务永远不可能在相同的行上持有冲突的锁。行级锁不影响数据查询，它们只阻塞对同一行的*写入者和加锁者*。 行级锁在事务结束时或保存点回滚的时候释放，就像表级锁一样。

行级锁模式

• FOR UPDATE

  FOR UPDATE会导致由SELECT语句检索到的行被锁定，就好像它们要被更新。这可以阻止它们被其他事务锁定、修改或者删除，一直到当前事务结束。也就是说其他尝试UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE这些行的事务将被阻塞，直到当前事务结束。反过来，SELECT FOR UPDATE将等待已经在相同行上运行以上这些命令的并发事务，并且接着锁定并且返回被更新的行（或者没有行，因为行可能已被删除）。不过，在一个REPEATABLE READ或SERIALIZABLE事务中，如果一个要被锁定的行在事务开始后被更改，将会抛出一个错误。进一步的讨论请见第 13.4 节。任何在一行上的DELETE命令也会获得FOR UPDATE锁模式，以及修改某些列的值的UPDATE也会获得该锁模式。 当前UPDATE情况中被考虑的列集合是那些具有能用于外键的唯一索引的列（所以部分索引和表达式索引不被考虑），但是这种要求未来有可能会改变。

• FOR NO KEY UPDATE

  行为与FOR UPDATE类似，不过获得的锁较弱：这种锁将不会阻塞尝试在相同行上获得锁的SELECT FOR KEY SHARE命令。任何不获取FOR UPDATE锁的UPDATE也会获得这种锁模式。

• FOR SHARE

  行为与FOR NO KEY UPDATE类似，不过它在每个检索到的行上获得一个共享锁而不是排他锁。一个共享锁会阻塞其他事务在这些行上执行UPDATE、DELETE、SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE，但是它不会阻止它们执行SELECT FOR SHARE或者SELECT FOR KEY SHARE。

• FOR KEY SHARE

  行为与FOR SHARE类似，不过锁较弱：SELECT FOR UPDATE会被阻塞，但是SELECT FOR NO KEY UPDATE不会被阻塞。一个键共享锁会阻塞其他事务执行修改键值的DELETE或者UPDATE，但不会阻塞其他UPDATE，也不会阻止SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE。

PostgreSQL不会在内存里保存任何关于已修改行的信息，因此对一次锁定的行数没有限制。 不过，锁住一行会导致一次磁盘写，例如， SELECT FOR UPDATE将修改选中的行以标记它们被锁住，并且因此会导致磁盘写入。

表 13.3. 冲突的行级锁

要求的锁模式	当前的锁模式
FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

13.3.3. 页级锁

除了表级别和行级别的锁以外，页面级别的共享/排他锁被用来控制对共享缓冲池中表页面的读/写。 这些锁在行被抓取或者更新后马上被释放。应用开发者通常不需要关心页级锁，我们在这里提到它们只是为了完整。

13.3.4. 死锁

显式锁定的使用可能会增加死锁的可能性，死锁是指两个（或多个）事务相互持有对方想要的锁。例如，如果事务 1 在表 A 上获得一个排他锁，同时试图获取一个在表 B 上的排他锁， 而事务 2 已经持有表 B 的排他锁，同时却正在请求表 A 上的一个排他锁，那么两个事务就都不能进行下去。PostgreSQL能够自动检测到死锁情况并且会通过中断其中一个事务从而允许其它事务完成来解决这个问题（具体哪个事务会被中断是很难预测的，而且也不应该依靠这样的预测）。

要注意死锁也可能会作为行级锁的结果而发生（并且因此，它们即使在没有使用显式锁定的情况下也会发生)。考虑如下情况，两个并发事务在修改一个表。第一个事务执行：

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;

这样就在指定帐号的行上获得了一个行级锁。然后，第二个事务执行：

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;

第一个UPDATE语句成功地在指定行上获得了一个行级锁，因此它成功更新了该行。 但是第二个UPDATE语句发现它试图更新的行已经被锁住了，因此它等待持有该锁的事务结束。事务二现在就在等待事务一结束，然后再继续执行。现在，事务一执行：

UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

事务一试图在指定行上获得一个行级锁，但是它得不到：事务二已经持有了这样的锁。所以它要等待事务二完成。因此，事务一被事务二阻塞，而事务二也被事务一阻塞：一个死锁。 PostgreSQL将检测这样的情况并中断其中一个事务。

防止死锁的最好方法通常是保证所有使用一个数据库的应用都以一致的顺序在多个对象上获得锁。在上面的例子里，如果两个事务以同样的顺序更新那些行，那么就不会发生死锁。 我们也应该保证一个事务中在一个对象上获得的第一个锁是该对象需要的最严格的锁模式。如果我们无法提前验证这些，那么可以通过重试因死锁而中断的事务来即时处理死锁。

只要没有检测到死锁情况，寻求一个表级或行级锁的事务将无限等待冲突锁被释放。这意味着一个应用长时间保持事务开启不是什么好事（例如等待用户输入）。

13.3.5. 咨询锁

PostgreSQL提供了一种方法创建由应用定义其含义的锁。这种锁被称为咨询锁，因为系统并不强迫其使用 — 而是由应用来保证其正确的使用。咨询锁可用于 MVCC 模型不适用的锁定策略。例如，咨询锁的一种常用用法是模拟所谓“平面文件”数据管理系统典型的悲观锁策略。虽然一个存储在表中的标志可以被用于相同目的，但咨询锁更快、可以避免表膨胀并且会由服务器在会话结束时自动清理。

有两种方法在PostgreSQL中获取一个咨询锁：在会话级别或在事务级别。一旦在会话级别获得了咨询锁，它将被保持直到被显式释放或会话结束。不同于标准锁请求，会话级咨询锁请求不尊重事务语义：在一个后来被回滚的事务中得到的锁在回滚后仍然被保持，并且同样即使调用它的事务后来失败一个解锁也是有效的。一个锁在它所属的进程中可以被获取多次；对于每一个完成的锁请求必须有一个相应的解锁请求，直至锁被真正释放。在另一方面，事务级锁请求的行为更像普通锁请求：在事务结束时会自动释放它们，并且没有显式的解锁操作。这种行为通常比会话级别的行为更方便，因为它使用一个咨询锁的时间更短。对于同一咨询锁标识符的会话级别和事务级别的锁请求按照期望将彼此阻塞。如果一个会话已经持有了一个给定的咨询锁，由它发出的附加请求将总是成功，即使有其他会话在等待该锁；不管现有的锁和新请求是处在会话级别还是事务级别，这种说法都是真的。

和所有PostgreSQL中的锁一样，当前被任何会话所持有的咨询锁的完整列表可以在pg_locks系统视图中找到。

咨询锁和普通锁都被存储在一个共享内存池中，它的尺寸由max_locks_per_transaction和max_connections配置变量定义。 必须当心不要耗尽这些内存，否则服务器将不能再授予任何锁。这对服务器可以授予的咨询锁数量设置了一个上限，根据服务器的配置不同，这个限制通常是数万到数十万。

在使用咨询锁方法的特定情况下，特别是查询中涉及显式排序和LIMIT子句时，由于 SQL 表达式被计算的顺序，必须小心控制锁的获取。例如：

SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
SELECT pg_advisory_lock(q.id) FROM
(
  SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok

在上述查询中，第二种形式是危险的，因为不能保证在锁定函数被执行之前应用LIMIT。这可能导致获得某些应用不期望的锁，并因此在会话结束之前无法释放。 从应用的角度来看，这样的锁将被挂起，虽然它们仍然在pg_locks中可见。

提供的操作咨询锁函数在第 9.27.10 节中描述。

14. 在 PostgreSQL 中，UPDATE 语句的 SET 子句中不能使用表别名来引用字段

在 PostgreSQL 中，UPDATE 语句的 SET 子句中不能使用表别名来引用字段。例如：

sql

复制

UPDATE "tenant_view_ref_model" t
SET t."view_meta_id" = 'value' -- 错误：不能使用 t."view_meta_id"
WHERE t.id = '04ifm5h3y4a13';

正确的写法应该是直接使用字段名，而不需要表别名：

sql

复制

UPDATE "tenant_view_ref_model"
SET "view_meta_id" = 'value' -- 正确：直接使用字段名
WHERE id = '04ifm5h3y4a13';

和oracle不同，pgsql的update语法中关联表不使用join ，而是使用from，关联条件不用on，而是写在where中，和条件写在一起。

使用别名更新时，被更新表的字段不能用别名，如m.code = n.code是不行的。

15.PostgreSQL 自定义自动类型转换(CAST)

PostgreSQL是一个强类型数据库，因此你输入的变量、常量是什么类型，是强绑定的，例如

在调用操作符时，需要通过操作符边上的数据类型，选择对应的操作符。

在调用函数时，需要根据输入的类型，选择对应的函数。

如果类型不匹配，就会报操作符不存在，或者函数不存在的错误。

postgres=# select '1' + '1';  
ERROR:  operator is not unique: unknown + unknown  
LINE 1: select '1' + '1';  
                   ^  
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.  

那么使用起来是不是很不方便呢？

PostgreSQL开放了类型转换的接口，同时也内置了很多的自动类型转换。来简化操作。

查看目前已有的类型转换：

postgres=# \dC+  
                                                List of casts  
         Source type         |         Target type         |      Function      |   Implicit?   | Description   
-----------------------------+-----------------------------+--------------------+---------------+-------------  
 "char"                      | character                   | bpchar             | in assignment |   
 "char"                      | character varying           | text               | in assignment |   
 "char"                      | integer                     | int4               | no            |   
 "char"                      | text                        | text               | yes           |   
 abstime                     | date                        | date               | in assignment |   
 abstime                     | integer                     | (binary coercible) | no            |   
 abstime                     | time without time zone      | time               | in assignment |   
  
 ................................  
  
 timestamp without time zone | timestamp with time zone    | timestamptz        | yes           |   
 timestamp without time zone | timestamp without time zone | timestamp          | yes           |   
 xml                         | character                   | (binary coercible) | in assignment |   
 xml                         | character varying           | (binary coercible) | in assignment |   
 xml                         | text                        | (binary coercible) | in assignment |   
(246 rows)  

注意Implicit列，实际上是pg_cast里面的context转换为可读的内容(e表示no, a表示assignment, 否则表示implicit)。

SELECT pg_catalog.format_type(castsource, NULL) AS "Source type",
       pg_catalog.format_type(casttarget, NULL) AS "Target type",
       CASE WHEN castfunc = 0 THEN '(binary coercible)'
            ELSE p.proname
       END as "Function",
       CASE WHEN c.castcontext = 'e' THEN 'no'
            WHEN c.castcontext = 'a' THEN 'in assignment'
            ELSE 'yes'
       END as "Implicit?"
FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p
     ON c.castfunc = p.oid
     LEFT JOIN pg_catalog.pg_type ts
     ON c.castsource = ts.oid
     LEFT JOIN pg_catalog.pg_namespace ns
     ON ns.oid = ts.typnamespace
     LEFT JOIN pg_catalog.pg_type tt
     ON c.casttarget = tt.oid
     LEFT JOIN pg_catalog.pg_namespace nt
     ON nt.oid = tt.typnamespace
WHERE (true  AND pg_catalog.pg_type_is_visible(ts.oid)
) OR (true  AND pg_catalog.pg_type_is_visible(tt.oid)
)
ORDER BY 1, 2;

如果你发现有些类型转换没有内置，怎么办呢？我们可以自定义转换。

当然你也可以使用这种语法，对类型进行强制转换：

CAST(x AS typename)   
  
  or   
  
x::typename  

如何自定义类型转换(CAST)

自定义CAST的语法如下：

CREATE CAST (source_type AS target_type)  
    WITH FUNCTION function_name [ (argument_type [, ...]) ]  
    [ AS ASSIGNMENT | AS IMPLICIT ]  
  
CREATE CAST (source_type AS target_type)  
    WITHOUT FUNCTION  
    [ AS ASSIGNMENT | AS IMPLICIT ]  
  
CREATE CAST (source_type AS target_type)  
    WITH INOUT  
    [ AS ASSIGNMENT | AS IMPLICIT ]  

解释：

1、WITH FUNCTION，表示转换需要用到什么函数。

2、WITHOUT FUNCTION，表示被转换的两个类型，在数据库的存储中一致，即物理存储一致。例如text和varchar的物理存储一致。不需要转换函数。

Two types can be binary coercible,   
which means that the conversion can be performed “for free” without invoking any function.   
  
This requires that corresponding values use the same internal representation.   
  
For instance, the types text and varchar are binary coercible both ways.   
  
Binary coercibility is not necessarily a symmetric relationship.   
  
For example, the cast from xml to text can be performed for free in the present implementation,   
but the reverse direction requires a function that performs at least a syntax check.   
  
(Two types that are binary coercible both ways are also referred to as binary compatible.)  

3、WITH INOUT，表示使用内置的IO函数进行转换。每一种类型，都有INPUT 和OUTPUT函数。使用这种方法，好处是不需要重新写转换函数。

除非有特殊需求，我们建议直接使用IO函数来进行转换。

                               List of functions  
   Schema   |      Name       | Result data type | Argument data types |  Type    
------------+-----------------+------------------+---------------------+--------  
 pg_catalog | textin          | text             | cstring             | normal  
 pg_catalog | textout         | cstring          | text                | normal  
 pg_catalog | date_in         | date             | cstring             | normal  
 pg_catalog | date_out        | cstring          | date                | normal  

You can define a cast as an I/O conversion cast by using the WITH INOUT syntax.   
  
An I/O conversion cast is performed by invoking the output function of the source data type,   
and passing the resulting string to the input function of the target data type.   
  
In many common cases, this feature avoids the need to write a separate cast function for conversion.   
  
An I/O conversion cast acts the same as a regular function-based cast; only the implementation is different.  

4、AS ASSIGNMENT，表示在赋值时，自动对类型进行转换。例如字段类型为TEXT，输入的类型为INT，那么可以创建一个 cast(int as text) as ASSIGNMENT。

If the cast is marked AS ASSIGNMENT then it can be invoked implicitly when assigning a value to a column of the target data type.   
  
For example, supposing that foo.f1 is a column of type text, then:  
  
INSERT INTO foo (f1) VALUES (42);  
  
will be allowed if the cast from type integer to type text is marked AS ASSIGNMENT,   
otherwise not.   
  
(We generally use the term assignment cast to describe this kind of cast.)  

5、AS IMPLICIT，表示在表达式中，或者在赋值操作中，都对类型进行自动转换。（包含了AS ASSIGNMENT，它只对赋值进行转换）

If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context,   
whether assignment or internally in an expression.   
  
(We generally use the term implicit cast to describe this kind of cast.)   
  
For example, consider this query:  
  
SELECT 2 + 4.0;  
  
The parser initially marks the constants as being of type integer and numeric respectively.   
  
There is no integer + numeric operator in the system catalogs, but there is a numeric + numeric operator.   
  
The query will therefore succeed if a cast from integer to numeric is available and is marked AS IMPLICIT —   
which in fact it is.   
  
The parser will apply the implicit cast and resolve the query as if it had been written  
  
SELECT CAST ( 2 AS numeric ) + 4.0;  

6、注意，AS IMPLICIT需要谨慎使用，为什么呢？因为操作符会涉及到多个算子，如果有多个转换，目前数据库并不知道应该选择哪个？

Now, the catalogs also provide a cast from numeric to integer.   
  
If that cast were marked AS IMPLICIT — (which it is not — )  
  
then the parser would be faced with choosing between the above interpretation and   
the alternative of casting the numeric constant to integer and applying the integer + integer operator.   
  
Lacking any knowledge of which choice to prefer, it would give up and declare the query ambiguous.   
  
The fact that only one of the two casts is implicit is the way in which we teach the parser to prefer resolution of   
a mixed numeric-and-integer expression as numeric;   
  
there is no built-in knowledge about that.  

因此，建议谨慎使用AS IMPLICIT。建议使用AS IMPLICIT的CAST应该是非失真转换转换，例如从INT转换为TEXT，或者int转换为numeric。

而失真转换，不建议使用as implicit，例如numeric转换为int。

It is wise to be conservative about marking casts as implicit.   
  
An overabundance of implicit casting paths can cause PostgreSQL to choose surprising interpretations of commands,   
or to be unable to resolve commands at all because there are multiple possible interpretations.   
  
A good rule of thumb is to make a cast implicitly invokable only for information-preserving   
transformations between types in the same general type category.   
  
For example, the cast from int2 to int4 can reasonably be implicit,   
but the cast from float8 to int4 should probably be assignment-only.   
  
Cross-type-category casts, such as text to int4, are best made explicit-only.  

注意事项 + 例子

不能嵌套转换。例子

1、将text转换为date

错误方法

create or replace function text_to_date(text) returns date as $$  
  select cast($1 as date);  
$$ language sql strict;  
  
create cast (text as date) with function text_to_date(text) as implicit;  

嵌套转换后出现死循环

postgres=# select text '2017-01-01' + 1;  
ERROR:  stack depth limit exceeded  
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.  
CONTEXT:  SQL function "text_to_date" during startup  
SQL function "text_to_date" statement 1  
SQL function "text_to_date" statement 1  
SQL function "text_to_date" statement 1  
......  

正确方法

create or replace function text_to_date(text) returns date as $$          
  select to_date($1,'yyyy-mm-dd');  
$$ language sql strict;  
  
create cast (text as date) with function text_to_date(text) as implicit;  

postgres=# select text '2017-01-01' + 1;  
  ?column?    
------------  
 2017-01-02  
(1 row)  

我们还可以直接使用IO函数来转换：

postgres=# create cast (text as date) with inout as implicit;
CREATE CAST

postgres=# select text '2017-01-01' + 1;
  ?column?  
------------
 2017-01-02
(1 row)

参考

https://www.postgresql.org/docs/10/static/sql-createcast.html

《PostgreSQL 整型int与布尔boolean的自动转换设置》

16 不兼容项

{
	"id": "guid",
	"jsonrpc": "2.0",
	"method": "service",
	"params": {
		"args": {
			"ids": [
				"rbac_test_2"
			],
			"values": {
				"a": "3",
				"lastName": "1",
				"b": 21,
				"c": 7.2,
				"d": "2023-01-16",
				"e": "abc",
				"html": "1",
				"testUser": "rbac_role_admin",
				"d2": "2023-01-20 09:10:10",
				"id": "rbac_test_2"
			},
			"useDisplayForModel": true
		},
		"context": {
			"uid": "",
			"timeZone": "UTC+8",
			"lang": "zh-CN"
		},
		"model": "TestTest1",
		"tag": "master",
		"service": "newSdkApp.TestTest1.TestTest_form.form:master#update",
		"app": "newSdkApp"
	}
}

aused by: org.postgresql.util.PSQLException: 错误: 当前事务被终止, 事务块结束之前的查询被忽略

​ at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)

​ at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364)

​ at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354)

​ at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484)

​ at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:404)

​ at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:162)

​ at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:151)

​ at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)

​ at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)

​ at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)

​ at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)

​ at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)

​ at com.sie.snest.engine.db.relationdb.RelationDBAccessor.doExecute(RelationDBAccessor.java:614)

​ … 82 common frames omitted

Caused by: org.postgresql.util.PSQLException: 错误: 字段 "age" 的类型为 integer, 但表达式的类型为 character varying

建议：你需要重写或转换表达式

位置：245

16.1 boolean值问题

pg支持boolean，但是我们引擎是使用char(1)代替boolean

16.2 索引问题，索引

1. 在 PostgreSQL 中，索引名称（Index Name）的最大长度受限于标识符（Identifier）的最大长度。根据 PostgreSQL 的官方文档，标识符的最大长度为 63 字节。

---

## 1. 标识符的最大长度

• PostgreSQL 中的标识符（如表名、列名、索引名等）最大长度为 63 字节。
• 如果标识符的长度超过 63 字节，PostgreSQL 会自动截断到 63 字节，并发出警告。

在 PostgreSQL 中，索引名称（Index Name）的命名规则和限制与其他数据库对象（如表、列、约束等）类似。以下是 PostgreSQL 索引命名的详细限制和最佳实践：

---

1. 索引命名的基本规则

• 长度限制：索引名称的最大长度为 63 字节。如果名称超过 63 字节，PostgreSQL 会自动截断并发出警告。
• 字符集：索引名称可以包含字母、数字、下划线（_）和其他特殊字符（如 $），但通常建议使用字母、数字和下划线。
• 大小写敏感性：索引名称是大小写不敏感的，但 PostgreSQL 会将名称转换为小写存储。如果需要保留大小写，可以使用双引号（"）包裹名称。
• 唯一性：在同一个 schema 中，索引名称必须是唯一的。

---

2. 索引命名的限制

(1) 长度限制

索引名称的最大长度为 63 字节。如果名称超过 63 字节，PostgreSQL 会截断名称并发出警告。

例如：

sql

复制

CREATE INDEX this_is_a_very_long_index_name_that_exceeds_the_maximum_length_of_sixty_three_bytes ON my_table(my_column);

PostgreSQL 会将其截断为：

复制

this_is_a_very_long_index_name_that_exceeds_the_maximum_length_of_sixty_three

(2) 大小写敏感性

默认情况下，索引名称会被转换为小写存储。如果需要保留大小写，可以使用双引号包裹名称。

例如：

sql

复制

CREATE INDEX "MyIndex" ON my_table(my_column);

此时，索引名称会保留为 MyIndex。

(3) 特殊字符

索引名称可以包含特殊字符（如 $、# 等），但通常不建议使用，因为可能会导致兼容性问题。

例如：

sql

复制

CREATE INDEX "my_index$1" ON my_table(my_column);

(4) 唯一性

在同一个 schema 中，索引名称必须是唯一的。如果尝试创建同名的索引，PostgreSQL 会抛出错误。

例如：

sql

复制

CREATE INDEX my_index ON my_table(my_column);
CREATE INDEX my_index ON my_table(another_column);  -- 错误：索引名称冲突

---

3. 索引命名的最佳实践

(1) 使用有意义的名称

索引名称应反映其用途，例如：

• idx_table_column：表示在 table 表的 column 列上创建的索引。
• uk_table_column：表示在 table 表的 column 列上创建的唯一约束索引。

(2) 避免使用保留字

避免使用 PostgreSQL 的保留字（如 SELECT、INSERT 等）作为索引名称。

(3) 使用一致的命名规则

为索引名称定义一致的命名规则，例如：

• 前缀：使用 idx_ 表示普通索引，uk_ 表示唯一索引，pk_ 表示主键索引。
• 表名和列名：在索引名称中包含表名和列名。

例如：

sql

复制

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX uk_users_username ON users(username);

(4) 避免过长的名称

尽量保持索引名称简洁，避免超过 63 字节的限制。

---

4. 修改索引名称

如果索引名称不符合要求，可以使用 ALTER INDEX 修改索引名称。

例如：

sql

复制

ALTER INDEX old_index_name RENAME TO new_index_name;

---

5. 查看索引名称

可以通过以下 SQL 查询当前数据库中的所有索引及其名称：

sql

复制

SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE schemaname NOT LIKE 'pg_%'  -- 排除系统 schema
ORDER BY schemaname, tablename, indexname;

---

6. 总结

• PostgreSQL 索引名称的最大长度为 63 字节。
• 索引名称在同一个 schema 中必须是唯一的。
• 默认情况下，索引名称会被转换为小写存储，可以使用双引号保留大小写。
• 建议使用有意义的命名规则，并避免使用特殊字符和保留字。

通过遵循这些规则和最佳实践，可以确保索引名称的可读性和一致性。

16.3. 事物问题

• PostgreSQL支持Transactional DDL

• Transactional DDL

  PostgreSQL supports transactional DDL (Data Definition Language) operations, such as CREATE, ALTER, and DROP statements. This means that schema-altering operations can be included within a transaction block and rolled back if required. In MySQL, DDL statements are typically not transactional. Thus, when an error occurs during a schema-altering operation, it cannot be rolled back.

  https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

  PostgreSQL支持Transactional DDL

  PostgreSQL运维技术2022-08-20

  1228

  什么是Transactional DDL?

  Transactional(事务)在关系型数据库是指一组SQL语句，要么提交，要么全部回滚。事务中包含的语句通常是DML语句，如INSERT、UPDATE、DELETE等。但是对于DDL语句呢？是否可以在事务中包含诸如CREATE、ALTER、DROP等DDL命令？

  所谓Transactional DDL就是我们可以把ddl放到事务中，做到事务中的ddl语句要么全部提交，要么全部回滚。

  PG是否支持Transactional DDL?

  看个pg的例子

  postgres=# begin;
  BEGIN
  postgres=*# create table a_test(id int);
  CREATE TABLE
  postgres=*# insert into a_test values(1);
  INSERT 0 1
  postgres=*# rollback;
  ROLLBACK
  postgres=# select * from a_test;
  ERROR:  relation "a_test" does not exist
  LINE 1: select * from a_test;
                        ^
  postgres=#

  可见，在postgresql中，是支持transactional ddl的，在上例中，create table语句被回滚掉了。

  并不是所有数据库都支持Transactional ddl，比如mysql。

  看个mysql的例子：

  mysql> begin;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql>  create table a_test (id int);
  Query OK, 0 rows affected (0.04 sec)
  
  mysql> insert into a_test values(1);
  Query OK, 1 row affected (0.01 sec)
  
  mysql> rollback;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from a_test;
  +------+
  | id   |
  +------+
  |    1 |
  +------+
  1 row in set (0.00 sec)

  可以看到mysql这个例子里，不仅create语句没有回滚掉，insert语句也没有回滚掉。这是因为：在mysql中，当执行ddl语句时，会隐式地将当前会话的事务进行一次commit操作。 所以我们应该严格地将DDL和DML完全分开，不能混合在一起执行。

  一些特例

  需要注意的是在pg中并不是所有的ddl都支持Transactional ddl。比如CREATE INDEX CONCURRENTLY、CREATE DATABAE 、CREATE TABLESPACE等等。

  postgres=# begin
  postgres-# ;
  BEGIN
  postgres=*# CREATE INDEX CONCURRENTLY idx_id ON a_test (id);
  ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

  Transactional DDL的好处

  在进行一些模式升级等复杂工作时，可以利用此功能保护数据库。我们可以将所有更改都放入事务块中，确保它们都以原子方式应用，或者根本不应用。这大大降低了数据库因模式更改中的输入错误或其他此类错误而损坏数据库的可能性。

  总结

  transactional ddl是指可以把ddl放到事务中，做到事务中的ddl语句要么全部提交，要么全部回滚。

  PG大部分ddl都支持Transactional ddl，除了一些CREATE INDEX CONCURRENTLY、CREATE DATABAE 、CREATE TABLESPACE等语句。

  参考：

  https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

  https://www.cybertec-postgresql.com/en/transactional-ddls/

16.4 数据类型转换

• PostgreSQL必须显示的进行数据类型转换

• 0 1 字符串，字符串不会显示的转换成boolean,不会显示的转换成数字

• caused by: org.postgresql.util.PSQLException: 错误: 字段 "age" 的类型为 integer, 但表达式的类型为 character varying

  建议：你需要重写或转换表达式

  位置：245