MySQL 切换 PostgreSQL 避坑指南：11 个高频语法与类型转换问题

项目原本的技术栈是 SpringBoot + MyBatisPlus + MySQL，后来因为业务需要把数据库整体迁到了 PostgreSQL。本来以为改下 JDBC 连接地址就完事了，结果真动起手来，SQL 语法差异、类型转换、事务行为，一个坑接着一个坑。这篇文章把切换流程和踩过的坑完整梳理一遍，给同样需要做迁移的同学提前排雷。

1、切换流程

切换的第一步是引入驱动包，第二步是改连接配置。听起来简单，但仅仅做到这两步，项目大概率是跑不起来的。

1.1、项目引入 PostgreSQL 驱动包

连接新数据库，自然要先引入 PostgreSQL 的 JDBC 驱动，和 MySQL 驱动包的思路一样：

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

1.2、修改 JDBC 连接信息

之前用的是 MySQL 协议，现在需要改成 PostgreSQL 的连接协议：

spring:
  datasource:
    # 修改驱动类
    driver-class-name: org.postgresql.Driver
    # 修改连接地址
    url: jdbc:postgresql://数据库地址/数据库名?currentSchema=模式名&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false

PostgreSQL 相比 MySQL 多了一层「模式（Schema）」的概念，一个数据库下面可以有多个模式。这里的模式名等价于以前 MySQL 的数据库名，如果不指定，默认就是 public。

到这里，连接层面的改造基本就做完了。但真正的挑战才刚刚开始——毕竟是两个完全不同的数据库，语法层面还有大量差异。接下来就是逐条修改项目里的 SQL，踩坑记录分享给大家。

2、踩坑记录

2.1、TIMESTAMPTZ 类型与 LocalDateTime 不匹配

异常信息：

PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type java.time.LocalDateTime.

如果 PostgreSQL 表的字段类型是 TIMESTAMPTZ，而 Java 对象的字段类型是 LocalDateTime，就会无法转换映射。PostgreSQL 表字段应该用 timestamp，或者 Java 字段改用 Date 类型。

2.2、参数值不能用双引号

错误示例：

WHERE name = "jay"   ===>   WHERE name = 'jay'

这里参数值 "jay" 应该改成单引号 'jay'。

2.3、字段不能用反引号包裹

错误示例：

WHERE `name` = 'jay'  ==>    WHERE name = 'jay'

MySQL 里习惯用反引号包裹字段名，但 PostgreSQL 不支持这种做法，字段名直接写即可。

2.4、JSON 字段处理语法不同

-- mysql语法:
WHERE keywords_json->'$.name' LIKE CONCAT('%', ?, '%')

-- postgreSQL语法:
WHERE keywords_json ->>'name' LIKE CONCAT('%', ?, '%')

获取 JSON 字段子属性的值时，MySQL 用的是 -> '$.xxx' 语法，而 PostgreSQL 得用 ->>'xx' 语法来选择属性。

2.5、convert 函数不存在

PostgreSQL 没有 convert 函数，需要用 CAST 函数替换：

-- mysql语法:
select convert(name, DECIMAL(20, 2))

-- postgreSQL语法:
select CAST(name as DECIMAL(20, 2))

2.6、force index 语法不存在

-- mysql语法
select xx FROM user force index(idx_audit_time)

MySQL 可以使用 force index 强制走索引，PostgreSQL 没有这个语法，建议直接去掉。

2.7、ifnull 函数不存在

PostgreSQL 没有 ifnull 函数，需要用 COALESCE 函数替换。

异常信息：

cause: org.postgresql.util.PSQLException: ERROR: function ifnull(numeric, numeric) does not exist

2.8、date_format 函数不存在

异常信息：

Cause: org.postgresql.util.PSQLException: ERROR: function date_format(timestamp without time zone, unknown) does not exist

PostgreSQL 没有 date_format 函数，需要用 to_char 函数替换。

替换示例：

// %Y => YYYY
// %m  =>   MM
// %d   =>  DD
// %H => HH24
// %i => MI
// %s => SS
to_char(time,'YYYY-MM-DD') => DATE_FORMAT(time,'%Y-%m-%d')
to_char(time,'YYYY-MM') => DATE_FORMAT(time,'%Y-%m')
to_char(time,'YYYYMMDDHH24MISS') => DATE_FORMAT(time,'%Y%m%d%H%i%s')

2.9、group by 语法问题

异常信息：

Cause: org.postgresql.util.PSQLException: ERROR: column "r.name" must appear in the GROUP BY clause or be used in an aggregate function

PostgreSQL 要求 select 的字段必须出现在 group by 的字段里，或者使用了聚合函数。MySQL 则没有这个硬性要求，非聚合列会随机取值。

错误示例：

select name, age, count(*)
from user
group by age, score

这时 select name 是错误的，因为 group by 里没有这个字段。要么把 name 加进 group by，要么改成 select min(name)。

2.10、事务异常问题

异常信息：

# Cause: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

; uncategorized SQLException; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

在 PostgreSQL 中，同一个事务里如果某次数据库操作出错了，那么这个事务后续的所有数据库操作都会报错。正常情况下不会遇到，但如果代码里捕获了事务异常后继续执行数据库操作，就会触发这个问题。MySQL 没有这个限制。

下面就是错误的代码写法——靠异常去走业务逻辑。解决办法就是不要依赖数据库异常来控制流程，尽量手动判断。

2.11、类型转换异常（大头）

这个可以说是最坑的。MySQL 支持自动类型转换，表字段类型和参数值类型不一致时会自动进行隐式转换。而 PostgreSQL 是强类型数据库，字段类型和参数值类型必须严格匹配，否则直接抛异常。

解决办法一般有两种：

• 手动修改代码里的字段类型和传参类型，或者调整 PostgreSQL 表字段类型，保证双方一一对应。
• 在数据库层面添加自动隐式转换函数，达到类似 MySQL 的效果。

1. select 查询时的转换异常信息

Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = boolean
SELECT   xx fom xx    WHERE enable = ture

错误原因：enable 字段是 smallint 类型，查询时却传了一个布尔值。

2. update 更新时的转换异常信息

Cause: org.postgresql.util.PSQLException: ERROR: column "name" is of type smallint but expression is of type boolean
update from xx set name = false  where  name = true

错误原因：在 update/insert 赋值语句中，字段类型是 smallint，但传参是布尔值类型。

解决办法：

在 PostgreSQL 数据库中添加 boolean <-> smallint 的自动转换逻辑：

-- 创建函数1  smallint到boolean到转换函数
CREATE OR REPLACE FUNCTION "smallint_to_boolean"("i" int2)
RETURNS "pg_catalog"."bool" AS $BODY$
BEGIN
RETURN (i::int2)::integer::bool;
END;
 $BODY$
LANGUAGE plpgsql VOLATILE
-- 创建赋值转换1
create cast (SMALLINT as BOOLEAN) with function smallint_to_boolean as ASSIGNMENT;

-- 创建函数2    boolean到smallint到转换函数
CREATE OR REPLACE FUNCTION "boolean_to_smallint"("b" bool)
RETURNS "pg_catalog"."int2" AS $BODY$
BEGIN
RETURN (b::boolean)::bool::int;
END;
 $BODY$
LANGUAGE plpgsql VOLATILE

-- 创建隐式转换2
create cast (BOOLEAN as SMALLINT) with function boolean_to_smallint as implicit;

如果想撤销，可以删除上面创建的函数和转换逻辑：

-- 删除函数
drop function smallint_to_boolean
-- 删除转换
drop CAST (SMALLINT as BOOLEAN)

要提醒一句：不要随意添加隐式转换函数，否则可能导致 Could not choose a best candidate operator 异常和 operator is not unique 异常——也就是操作符比较时有多个转换逻辑可用，数据库不知道该选哪个，反而陷入死循环。

3、PostgreSQL 辅助脚本

3.1、批量修改 timestamptz 脚本

批量把表字段类型 timestamptz 改成 timestamp，因为前者无法与 LocalDateTime 对应。

补充说明：

• timestamp without time zone 就是 timestamp
• timestamp with time zone 就是 timestamptz

DO $$
DECLARE
    rec RECORD;
BEGIN
FOR rec IN SELECT table_name, column_name,data_type
FROM information_schema.columns
where table_schema = '要处理的模式名'
AND data_type = 'timestamp with time zone'
LOOP
EXECUTE 'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' TYPE timestamp';
END LOOP;
END $$;

3.2、批量设置时间默认值脚本

批量把模式名下所有 timestamp 类型、且字段名为 create_time 或 update_time 的字段，默认值设置为 CURRENT_TIMESTAMP：

-- 注意 || 号拼接的后面的字符串前面要有一个空格
DO $$
DECLARE
    rec RECORD;
BEGIN
FOR rec IN SELECT table_name, column_name,data_type
FROM information_schema.columns
where table_schema = '要处理的模式名'
AND data_type = 'timestamp without time zone'
-- 修改的字段名
and column_name in ('create_time','update_time')
LOOP
EXECUTE 'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' SET DEFAULT CURRENT_TIMESTAMP;';
END LOOP;
END $$;

4、注意事项

1. 将数据表从 MySQL 迁移到 PostgreSQL 时，要注意字段类型对应，不要随意变更。
2. 原先是 tinyint 的就改成 smallint 类型，不要用 bool 类型，否则代码字段类型可能对应不上。
3. 如果 Java 字段是 LocalDateTime，原先 MySQL 的时间类型迁移到 PostgreSQL 后不要用 TIMESTAMPTZ 类型。
4. MySQL 一般用 tinyint 类型和 Java 的 Boolean 字段对应，并且在查询和更新时支持自动转换；但 PostgreSQL 是强类型，不支持这种隐式转换。如果想无缝迁移，可以在 PostgreSQL 内部新增自动转换的隐式函数，但缺点是每次部署 PostgreSQL 后都要去执行一次脚本。如果不想这样，只能修改代码里所有表对象的字段类型和传参类型，保证与 PostgreSQL 数据库的字段类型一一对应。但有些依赖框架底层自己操作数据库，可能无法修改源码，那就只能去调整数据库表字段类型了。

---

数据库迁移从来不只是换一个连接串的事。从 MySQL 到 PostgreSQL，SQL 方言、类型系统、事务语义的差异都可能在运行时才暴露出来。如果你也在做类似的数据库/中间件技术栈迁移，建议先把上述差异点梳理清楚，再逐条验证业务 SQL，避免上线后才发现问题。更完整的数据库迁移避坑指南也可以提前储备一份，关键时刻能少踩不少坑。