PostgreSQL 版本: 16.6

下载地址: Open-Source, Enterprise Postgres Database Management

第一节.本地和Docker安装步骤

1. 本地安装 PG

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

  1. 下载安装包

  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=******

参考资料

2. Docker 安装 PG

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

  1. 拉取镜像

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

    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=mysecretpassword -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. 进入容器

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

    docker exec -it postgres16 bash

  4. 连接到数据库

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

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

  5. DBCP 配置

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

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

参考资料

第二节. PostgreSQL 特性

2.0 特别注意:PostgreSQL 和 MySQL 中标识符大小写与引号使用对比

1. PostgreSQL 中的处理方式

表名和字段名大小写

  • 默认行为：PostgreSQL 在 SQL 查询时会自动将大写标识符转换为小写

  • 双引号影响：如果标识符用双引号括起来，则保留原始大小写

    SELECT * FROM MyTable;    -- 实际查询的是 mytable
SELECT * FROM "MyTable";  -- 查询的是保留大小写的 MyTable

引号使用

  • 单引号 ('')：用于字符串值
  • 双引号 ("")：用于标识符（表名、列名等）
  • 反引号 (``)：PostgreSQL 不支持使用反引号

2. MySQL 中的处理方式

表名大小写敏感性

  • Windows 系统：默认不区分大小写
  • Linux 系统：默认区分大小写
  • 控制参数：lower_case_table_names 参数控制此行为

列名大小写

  • 所有系统：列名不区分大小写

    SELECT mycolumn FROM mytable;  -- 与 SELECT MyColumn FROM MyTable 相同

引号使用

  • 单引号 ('') 和 双引号 ("")：都可用于字符串值

  • 反引号 (``)：用于标识符（表名、列名等），特别是在标识符与关键字冲突或包含特殊字符时

    SELECT `select` FROM `table`;

标识符大小写敏感性

  • 未加引号的标识符：大小写不敏感

    UPDATE MY_TABLE SET A = 5;  -- 等价于 uPDaTE my_TabLE SeT a = 5;

  • 加引号的标识符：保留大小写

    UPDATE "my_table" SET "a" = 5;  -- 必须使用相同大小写引用

主要差异总结

特性 PostgreSQL MySQL
标识符默认大小写处理 大写转小写 取决于系统和配置
保留大小写的方式 使用双引号 使用反引号
字符串引号 仅单引号 单引号或双引号
标识符引号 双引号 反引号
列名大小写敏感性 取决于是否加双引号 所有系统都不区分大小写

2.1 Boolean 值问题

PostgreSQL 支持 boolean 类型，但我们引擎是使用 char(1) 代替 boolean。插入到数据的时候我们插入 char(1)，查询的时候返回 boolean 值。

CREATE TABLE example (
    is_active CHAR(1)
);

2.2 索引问题

在 PostgreSQL 里，索引名称的最大长度受标识符最大长度限制，官方规定标识符最大长度为 63 字节。若标识符（如表名、列名、索引名等）长度超 63 字节，会自动截断并发出警告。

1. PostgreSQL 索引命名规则与实践

  1. 基本规则

    • 长度：索引名最长 63 字节，超量自动截断并告警
    • 字符集：可含字母、数字、下划线和特殊字符，建议只用前三者
    • 大小写：默认转小写存储，用双引号包裹可保留大小写
    • 唯一性：在同一个 schema 中，索引名称必须唯一，创建同名索引会引发错误

  2. 命名限制

    • 长度：长度超过 63 字节会被截断。

      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

    • 大小写：默认转小写，用 "MyIndex" 可保留

    • 特殊字符：可含特殊字符，但可能有兼容性问题，如 "my_index$1"

    • 唯一性：同一 schema 内重名报错，如 CREATE INDEX my_index ON my_table(my_column); 后再创建同名索引会冲突。

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

  3. 最佳实践

    • 索引名称应反映其用途：如 idx_table_column 表示普通索引，uk_table_column 表示唯一约束索引。例如：

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

    • 避保留字：不用 SELECT、INSERT 等保留字命名

    • 使用一致的命名规则：

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

      例如：

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

    • 长度限制：避免超过 63 字节

  4. 修改索引名

    使用 ALTER INDEX old_index_name RENAME TO new_index_name; 修改

  5. 查看索引名

    用以下 SQL 查询：

    SELECT schemaname, tablename, indexname
FROM pg_indexes
WHERE schemaname NOT LIKE 'pg_%'
ORDER BY schemaname, tablename, indexname;

2.3 支持Transactional DDL

1. 参考资料

2. 什么是 Transactional DDL？

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

所谓 Transactional DDL 就是我们可以把 DDL 放到事务中，做到事务中的 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 语句时，会隐式地将当前会话的事务进行一次提交操作。所以我们应该严格地将 DDL 和 DML 完全分开，不能混合在一起执行。

一些特例

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

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

3. Transactional DDL 的好处

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

4. PG 中的事务性 DDL: 数据库对比

事务性 DDL

与一些商业数据库一样，PostgreSQL 中有一个比较高级的功能，它能够通过其 预写式日志 的设计执行事务性 DDL。该设计支持回退 DDL 产生的重大更改，例如表创建。您无法恢复添加和删除数据库或表空间的操作，但所有其他的对象操作都是可逆的。

PostgreSQL

下面是一个示例，显示了 PostgreSQL 的设计在这方面的处理能力：

DROP TABLE IF EXISTS foo;
NOTICE: table "foo" does not exist

BEGIN;
CREATE TABLE foo (bar int);
INSERT INTO foo VALUES (1);
ROLLBACK;

SELECT * FROM foo;
ERROR: relation "foo" does not exist

有经验的 PostgreSQL DBA 知道，在执行表结构升级等复杂工作时，可利用此功能来保护自己。如果你把所有这些变更都放到一个事务块中，你可以确保它们都以原子方式进行应用，或者完全不应用。这大大降低了数据库因架构变更中的拼写错误或其他此类错误而损坏的可能性，这在修改多个相关表时尤为重要，因为错误可能会破坏关系键。

MySQL

如果您在使用 MySQL，则无法以这种方式撤消 DDL 和一些类似的更改。如果您使用的是 MyISAM 存储引擎，那它根本不支持事务。对于 InnoDB 存储引擎，服务端有一种 隐式提交，即使关闭了正常的自动提交行为，DDL 命令也会导致当前事务发生隐式提交。

set autocommit = 0;

drop table foo;

create table foo (bar int) engine=InnoDB;

insert into foo values (1);

rollback;

select * from foo;
Empty set (0.00 sec)

Oracle

一个事务从第一个可执行的 SQL 语句开始。事务在提交或回滚时结束，无论是显式使用 COMMIT 或 ROLLBACK 语句，还是在发出 DDL 语句时隐式提交。

Oracle 数据库在以下情况下会发生隐式 COMMIT：

  • 在任何语法上有效的数据定义语言（DDL）的语句之前，即使该语句发生了错误
  • 在任何没有发生错误执行完成的数据定义语言（DDL）语句之后

让我们在 Oracle 中创建一个表，并插入一行：

-- Create a table and insert a row
CREATE TABLE states
(
    abbr CHAR(2),
    name VARCHAR2(90)
);

-- Transaction will be in progress after this insert
INSERT INTO states VALUES ('CA', 'California');

现在，让我们创建另一个表，并执行 ROLLBACK 操作：

-- Create another table table and insert a row
CREATE TABLE cities
(
    name VARCHAR2(90),
    state CHAR(2)
);

INSERT INTO cities VALUES ('San Francisco', 'CA');

ROLLBACK;

您可以看到，即使在 ROLLBACK 之后，表 states 和表中的行仍然存在，因为 CREATE TABLE cities 语句提交了事务。

表 cities 也存在，但插入的行已回滚：

-- Table states exists and contains 1 row
SELECT COUNT(*) FROM states;
-- Result: 1

-- Table cities also exists, but the inserted row was rolled back
SELECT COUNT(*) FROM cities;
-- Result: 0

2.4 数据类型转换规则

PostgreSQL 的自动类型转换规则是比较严格的，尤其是从字符串到其他类型的转换，通常需要显式指定 PostgreSQL 要求显式的数据类型转换。

1. 示例：数据类型转换错误

INSERT INTO a_test (age) VALUES ('123'); -- 错误：字段 "age" 的类型为 integer, 但表达式的类型为 character varying

2. 解决方法

在插入或更新数据时，确保数据类型匹配，或使用显式类型转换：

INSERT INTO a_test (age) VALUES (CAST('123' AS INTEGER));

3. 自动类型转换（Implicit Casting）

PostgreSQL 会在某些情况下自动进行数据类型转换，以确保操作的正确性。这些情况主要是为了匹配操作符或函数的参数类型。

  1. 字符串到数字的隐式转换（受限）：

    • PostgreSQL 不会自动将字符串转换为数字类型（如 integer 或 numeric）。例如：

      SELECT '123' + 456; -- 报错：类型不匹配

      如果要进行这种操作，必须显式转换类型。

  2. 字符串与字符串的连接：

    • 如果两个操作数都是字符串类型，可以自动进行连接操作：

      SELECT 'Hello' || 'World'; -- 结果为 'HelloWorld'

  3. 字符串与其他类型的隐式转字符串：

    • 如果一个操作符或函数期望一个字符串类型的参数，而提供的是一个非字符串类型，PostgreSQL 会尝试将其隐式转换为字符串：

      SELECT 42 || ' apples'; -- 结果为 '42 apples'

  4. 显式类型转换（Explicit Casting）

显式类型转换是 PostgreSQL 中推荐的做法，用于明确地将数据从一种类型转换为另一种类型。以下是几种常用的显式类型转换方法：

  1. 使用 CAST 函数：

    • CAST 是标准 SQL 提供的类型转换函数。例如：

      SELECT CAST('123' AS INTEGER); -- 结果为 123

      你也可以指定其他目标类型，如 DOUBLE PRECISION、DATE 等：

      SELECT CAST('3.1415' AS NUMERIC(5,2)); -- 结果为 3.14

  2. 使用列定义语法：

    • 使用列定义语法也可以进行类型转换。例如：

      SELECT '2024-01-01'::DATE; -- 结果为 2024-01-01

      这种方法与 CAST 功能相同，但语法更简洁。

  3. 使用 :: 运算符：

    • :: 是 PostgreSQL 提供的快捷方式，用于类型转换。例如：

      SELECT '25'::integer; -- 结果为 25

  4. 类型转换的注意事项

  5. 避免非显式转换：

    • 尽量避免依赖隐式类型转换，因为它可能导致意外的错误。例如：

      INSERT INTO users (age) VALUES ('25'); -- 报错，除非 `age` 是 `VARCHAR` 类型

      如果 age 是 BOOLEAN 类型，而你尝试插入 TRUE，必须显式转换为布尔值：

      INSERT INTO users (age) VALUES (TRUE::BOOLEAN);

  6. 处理文本与日期/时间：

    • 当将字符串转换为日期或时间类型时，必须遵循正确的格式。例如：

      SELECT CAST('2023-10-05' AS DATE); -- 正确
SELECT CAST('10/05/2023' AS DATE); -- 抛出错误，因为格式不匹配

      如果需要将日期以特定格式存储或解析，可以使用 TO_DATE 函数：

      SELECT TO_DATE('05-OCT-2023', 'DD-MON-YYYY'); -- 结果为 2023-10-05

  7. 数组类型的转换：

    • 数组之间的类型转换通常比标量类型更复杂。例如，将 {1,2,3} 转换为 int[] 是可以直接的：

      SELECT '{1,2,3}'::int[]; -- 正确

      但是，将字符串数组转换为其他类型数组可能需要额外的处理。

  8. 类型转换失败：

    • 如果转换无法完成，PostgreSQL 会抛出错误。例如：

      SELECT CAST('text' AS INTEGER); -- 抛出错误：invalid input syntax for type integer

  9. 示例

以下是一些常见的类型转换示例：

  1. 将字符串转换为整数：

    SELECT CAST('42' AS INTEGER); -- 结果为 42
SELECT '42'::integer; -- 同上

  2. 将数字转换为字符串：

    SELECT CAST(42 AS VARCHAR); -- 结果为 '42'

  3. 将布尔值转换为整数（逻辑上不推荐，但支持）：

    SELECT CAST(TRUE AS INTEGER); -- 结果为 1
SELECT CAST(FALSE AS INTEGER); -- 结果为 0

  4. 将字符串转换为布尔值：

    SELECT CAST('t' AS BOOLEAN); -- 结果为 TRUE
SELECT CAST('false' AS BOOLEAN); -- 结果为 FALSE

2.5 PostgreSQL 错误：current transaction is aborted, commands ignored until end of transaction block

1. 错误概述

当 PostgreSQL 事务中的某条 SQL 语句执行失败时，整个事务会进入 中止状态（Aborted）。所有后续的 SQL 命令都会被忽略，直到事务被回滚或关闭。此时任何后续操作都会触发以下错误：current transaction is aborted, commands ignored until end of transaction block。 核心问题：事务未正确终止（未提交或回滚），导致数据库连接处于不可用状态。

错误信息:

caused by: org.postgresql.util.PSQLException: 错误: 当前事务被终止, 事务块结束之前的查询被忽略
current transaction is aborted, commands ignored until end of transaction block

2. 事务块中语句错误的回滚行为:数据库对比

摘要：在本文中，我们将学习事务块中语句错误的回滚行为，以及 PostgreSQL 和 Oracle 之间的区别。

参考文章

通常，您制造的任何错误都会引发异常，并导致当前事务被标记为中止。这是理智和预期的行为，但如果它发生在您运行大型事务并输入了错误内容时，这可能会非常的烦人！此时，您唯一能做的就是回滚事务并丢弃所有工作。

当您在 PostgreSQL 中遇到错误时，事务是无法继续的。例如：

DROP TABLE IF EXISTS demo;

START TRANSACTION;
CREATE TABLE demo(n, t) AS SELECT 1 n, current_timestamp t;
ALTER TABLE demo ADD UNIQUE(n);

SELECT * FROM demo;
 n |               t
---+-------------------------------
 1 | 2021-06-26 18:15:07.207671+08
(1 row)

INSERT INTO demo VALUES (2, current_timestamp);
SELECT * FROM demo;
 n |               t
---+-------------------------------
 1 | 2021-06-26 18:15:07.207671+08
 2 | 2021-06-26 18:15:07.207671+08
(2 rows)

INSERT INTO demo VALUES (1, current_timestamp);
ERROR: duplicate key value violates unique constraint "demo_n_key"
DETAIL: Key (n)=(1) already exists.

SELECT * FROM demo;
ERROR: current transaction is aborted, commands ignored until end of transaction block

ROLLBACK;

SELECT * FROM demo;
ERROR: relation "demo" does not exist
LINE 1: SELECT * FROM demo;

在这里我们进行了回滚。但其实也可以提交来终止事务，但它无论如何都会回滚：

COMMIT;

SELECT * FROM demo;
ERROR: relation "demo" does not exist
LINE 1: SELECT * FROM demo;

Oracle 语句级回滚

如果在执行过程中的任何时候，SQL 语句发生错误，则该语句的所有更改都将回滚。回滚的效果就好像该语句从未执行过一样。这是一种语句级的回滚。

在 SQL 语句执行过程中发生的错误会导致语句级回滚。（此类错误的一个示例是，尝试在主键中插入重复值。在 SQL 语句解析过程中发生的错误（如语法错误）尚未执行，因此不会导致语句级回滚。涉及死锁（争用相同数据）的单个 SQL 语句也可能导致语句级回滚。

失败的 SQL 语句只会导致丢失它自己执行的任何工作；它不会导致在当前事务中丢失之前的任何工作。如果该语句是 DDL 语句，则不会撤消紧接在它前面的隐式提交。

在 Oracle 中，在一次用户调用中有一个语句失败时，将会回滚该用户调用所做的修改，但不会回滚之前的修改。事务还可以继续（如重试，或执行替代的更改），例如：

CREATE TABLE DEMO AS SELECT 1 n, current_timestamp t FROM dual;
ALTER TABLE DEMO ADD UNIQUE(n);

SELECT * FROM DEMO;
         N T
---------- -----------------------------------
         1 02-AUG-21 11.04.16.507292 PM +00:00

INSERT INTO DEMO VALUES (2, current_timestamp);

SELECT * FROM DEMO;
         N T
---------- -----------------------------------
         1 02-AUG-21 11.04.16.507292 PM +00:00
         2 02-AUG-21 11.04.16.601105 PM +00:00

INSERT INTO DEMO VALUES (1, current_timestamp);
ERROR at line 1:
ORA-00001: unique constraint (DEMO.SYS_C007847) violated

SELECT * FROM DEMO;
         N T
---------- -----------------------------------
         1 02-AUG-21 11.04.16.507292 PM +00:00
         2 02-AUG-21 11.04.16.601105 PM +00:00

3. 错误原因

3.1 直接原因

  • 事务中的 SQL 语句执行失败：如违反唯一约束、主键冲突、语法错误、权限不足等
  • 事务未显式终止：失败后未执行 ROLLBACK 或 COMMIT，事务块未关闭

3.2 常见触发场景

场景 示例
插入重复的主键或唯一键 INSERT INTO users (id) VALUES (1);（若 id=1 已存在）
语法错误 INSERT INTO table (invalid_column) VALUES (1);（列名错误）
权限不足 普通用户尝试执行 DROP TABLE
隐式事务未处理 ORM 框架或客户端工具默认开启事务，但未捕获异常并回滚

4. 解决方法

1. 立即终止事务

通过 ROLLBACK 显式回滚事务，恢复数据库连接的可用状态：

ROLLBACK; -- 终止当前事务，清除错误状态

2. 定位错误原因

  1. 查看客户端返回的错误信息

    例如：duplicate key value violates unique constraint "users_pkey"

  2. 查询 PostgreSQL 日志

    • 日志默认路径：/var/log/postgresql/postgresql-<版本>-main.log
    • 搜索关键词：ERROR, ROLLBACK, 或事务 ID（如 process 12345）

  3. 通过 SQL 获取最近错误

    SELECT pg_last_error(); -- 部分客户端支持（如 psql）

3. 修复并重试事务

根据错误原因调整 SQL 逻辑：

  • 主键冲突：检查重复数据，或使用 ON CONFLICT 处理冲突。

    INSERT INTO users (id) VALUES (1)
ON CONFLICT (id) DO NOTHING;

  • 权限问题：授予用户权限。

    GRANT INSERT, UPDATE ON table_name TO user_name;

5. 高级场景与优化

1. 使用保存点（SAVEPOINT）

在长事务中分割操作，允许部分回滚：

BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint; -- 设置保存点
INSERT INTO table2 VALUES (1); -- 假设此处失败
ROLLBACK TO my_savepoint; -- 回滚到保存点，继续后续操作
INSERT INTO table3 VALUES (1);
COMMIT;

6. 预防措施

措施 说明
短事务原则 减少事务执行时间，避免长事务占用锁资源。
自动提交模式 在客户端工具中开启自动提交（SET AUTOCOMMIT = ON）。
异常捕获与回滚 在所有数据库操作中强制捕获异常并回滚。
测试与监控 定期模拟错误场景（如主键冲突），验证回滚逻辑是否生效。
日志分析 使用工具（如 pgBadger）分析 PostgreSQL 日志，识别高频错误。

2.6 PostgreSQL JSON数据类型

PostgreSQL 提供了强大的 JSON 支持，能够灵活地存储和处理 JSON 数据，同时结合了关系型数据库的可靠性和 JSON 数据的灵活性。

[JSON 数据类型]

1. JSON 数据类型

PostgreSQL提供了两种主要的JSON数据类型：

  • JSON：以文本格式存储JSON数据，保留输入时的所有细节（如空格和重复键）。适合需要保留原始文档格式的情况。
  • JSONB：将JSON数据解析并以二进制格式存储，不保留格式信息（如空格）和重复键。尽管在插入时可能稍慢，但读取和查询性能通常优于JSON类型，因此推荐用于大多数场景。

2. 查询JSON数据

PostgreSQL为JSON数据提供了丰富的操作符和函数，使得查询变得简单而强大。以下是一些常用的查询方法：

  • 提取数据：

    • ->：从JSON对象中获取指定键的值（返回JSON类型）。

    • ->>：类似->，但返回的是文本类型。

    • #> 和 #>>：用于根据提供的路径数组提取嵌套的JSON数据。

      由于->运算符返回 JSON 对象，因此您可以将其与运算符->>串接起来以检索特定节点。例如，以下语句返回所有已售出的产品：

      SELECT info -> 'items' ->> 'product' as product
FROM orders
ORDER BY product;

  • 检查包含关系：

    • @>：检查左侧的JSON是否包含右侧的JSON。
    • <@：与@>相反，检查左侧的JSON是否被右侧的JSON所包含。

  • 键存在性检查：

    • ?：检查JSON对象中是否存在某个键。

    • ?| 和 ?&：分别用于检查一组键中是否有任意一个或全部存在于JSON对象中。

3. 创建索引提高性能

为了优化JSON数据的查询性能，特别是对于复杂查询条件，PostgreSQL允许为JSONB字段创建GIN（Generalized Inverted Indexes）索引。GIN索引特别适用于处理复杂的查询，例如查找包含特定键值对的记录。

  • 创建GIN索引的例子：

    CREATE INDEX idx_jsonb_data ON your_table USING GIN (jsonb_column);

使用GIN索引可以显著加快涉及@>、?等操作符的查询速度，这对于需要频繁进行这类查询的应用来说是非常重要的。

2.7 PostgreSQL UPDATE 语句中,更新列名时不能带别名

请注意，在 PostgreSQL 中，虽然可以在 UPDATE 语句中为表指定别名，但在实际使用这些列名时，并不需要（也不应该）通过别名来引用它们. 条件可以带别名,但是列名不能带别名.

UPDATE tenant_user_instance t
SET t."update_date" = TO_TIMESTAMP('2025-02-18 11:00:27.296', 'YYYY-MM-DD HH24:MI:SS.FF')
WHERE t."action_dimension_id" = '04me8wsxbgrqt'
AND t."user_id" = '04me9sszlk54b'
AND t."instance_id" = '04me86gy87lxe'
AND t."source" IN ('USER', 'BUSINESS_APP');

错误 [42703]: ERROR: column "t" of relation "tenant_user_instance" does not exist 表示在 tenant_user_instance 表中找不到名为 "t" 的列。实际上，这个错误通常是由表别名的误用引起的。在 PostgreSQL 中，更新语句可以使用表别名，但不需要像在 SELECT 语句中那样引用别名作为表名的一部分。

以下是修正后的 SQL 语句，移除了对别名 "t" 的不正确引用：


UPDATE tenant_user_instance t
SET update_date = TO_TIMESTAMP('2025-02-18 11:00:27.296', 'YYYY-MM-DD HH24:MI:SS.FF')
WHERE t.action_dimension_id = '04me8wsxbgrqt'
AND t.user_id = '04me9sszlk54b'
AND t.instance_id = '04me86gy87lxe'
AND t.source IN ('USER', 'BUSINESS_APP');

2.8 select as 别名问题,如果别名不带标识符``或者"",PostgreSQL/gaussdb会将别名转成小写

请注意，在 PostgreSQL/GaussDB 中，关键词和不被引号修饰的标识符是大小写不敏感的。因此,如果字段不带修饰符双引号,会转换成小写.

如果自定义SQL列别名不带修饰符,这样就会导致代码获取displayName值为NULL, get("displayName")=NULL.就会导致数据库迁移的时候,不兼容.

正确的SQL写法,列别名如果是驼峰或者包含大小写的,一定要用 ""引起来.

参考 4.1.1. 标识符和关键词

select display_name as displayName from meta_app;

--MySQL
displayName   |
-----------------+
基础模块      |



--PostgreSQL/GaussDB
displayname   |
-----------------+
基础模块  


正确的SQL写法,列别名如果是驼峰或者包含大小写的
MYSQL: select display_name as `displayName` from meta_app;
PG/GAUSSDB/ORACLE: select display_name as "displayName" from meta_app;

项目自定义SQL错误的示范:
SELECT param_name paramName, param_value paramValue FROM base_process_spec bps;

标准的SQL语法:
SELECT param_name "paramName", param_value "paramValue" FROM base_process_spec bps

第三节. PostgreSQL 基础教程

来源: PostgreSQL 教程

本 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 节. 公共表表达式

第 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 数据进行导入和导出。

第 12 节. 管理表

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

第 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 节. 故障处理

第 18 节. PostgreSQL 技巧

第四节. 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 数据库进行交互。

Table of Contents