MySQL和PostgreSQL

首先，两个数据区连接驱动不一样，选用相应的依赖即可

语法区别：

1.TIMESTAMPTZ类型与LocalDateTime不匹配，异常信息如下：

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

如果postgres表的字段类型是TIMESTAMPTZ ，但是java对象的字段类型是LocalDateTime， 这时会无法转换映射上。postgres表字段类型应该用timestamp 或者 java字段类型用Date

2.参数值不能用双引号

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

3.字段不能用``包起来

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

4.json字段处理语法不同

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

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

5.convert函数不存在

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

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

6.force index 语法不存在

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

mysql可以使用force index强制走索引， postgres没有，建议去掉

7.ifnull 函数不存在，异常信息如下：

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

postgreSQL没有ifnull函数，用COALESCE函数替换

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函数替换

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 的 selectd的字段必须是group by的字段里的 或者使用了聚合函数。mysql则没有这个要求，非聚合列会随机取值

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

Postgres数据库中，同一事务中如果某次数据库操作中出错的话，那这个事务以后的数据库操作都会出错。正常来说不会有这种情况，但是如果有人去捕获了事务异常后又去执行数据库操作就会导致这个问题。mysql貌似不会有这个问题

11.类型转换异常 （大头）

这个可以说是最坑的， 因为mysql是支持自动类型转换的。在表字段类型和参数值之间如果类型不一样也会自动进行转换。而postgreSQL是强数据类型，字段类型和参数值类型之间必须一样否则就会抛出异常。

这时候解决办法一般有两种

• 手动修改代码里的字段类型和传参类型保证 或者 postgreSQL表字段类型，反正保证双方一一对应
• 添加自动隐式转换函数，达到类似mysql的效果

布尔值和int类型类型转换错误

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，但是传参却是布尔值类型

解决办法：

postgres数据库添加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 异常 就是在操作符比较的时候有多个转换逻辑不知道用哪个了，死循环了