Oracle2PostgreSQL数据库迁移

Oracle2PostgreSQL

记录一次从Oracle数据库迁移到pgsql数据库的项目相关调整。
数据库迁移用的是Navicat的数据传输工具，比较简单，可自行百度，需要注意的就是Oracle数据结构number(0,0) 迁移到pgsql之后会变成numeric(1000,53)，需要先处理数据结构再迁移数据。

一、Nacos相关配置修改

     # pgsql主库数据源
      master:
        driver-class-name: org.postgresql.Driver
        url: jdbc:postgresql://127.0.0.1:5432/postgres
        username: postgres
        password: sunsoft

     # validationQuery: SELECT 1 FROM DUAL 修改为：
     validationQuery: SELECT NOW()

二、postgres驱动依赖

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.5</version>
        <scope>runtime</scope>
    </dependency>

三、语法（ORACLE → PostgreSQL）

1. NVL() → coalesce()
2. trunc(时间) → date_trunc()

PZ_RQ <= TRUNC(#{dateEnd})+1-1/86400 
-- 改为（结果：2024-03-15 23:59:59）
PZ_RQ <= date_trunc('day', now()::timestamp) + interval '1 day' - interval '1 second';

3. 字符串连接符 || → concat()函数替代，oracle： ‘a’||null 结果是’a’, pgsql: ‘a’||null 结果是null,这种场景需要用concat替换||
4. SYSDATE → localtimestamp, now()
5. wm_concat(r.JSMC) → string_agg(r.JSMC,‘,’)
6. 分页查询：oracle用rownum，pg用limit
7. to_char, to_number,to_date：oracle自动格式转换，pg需指定格式

SELECT CAST('123' AS INTEGER); -- 将字符串 '123' 转换为整数类型
SELECT CAST('123.45' AS FLOAT); -- 将字符串 '123.45' 转换为浮点数类型

8. pgsql中，查询语句from子句中，表名后可以加 as 别名，oracle中表名后不允许出现as
9. 子查询，pgsql要求严格，必须具有别名才可以
10. oracle中的空字符串等同于null，pgsql中是分开处理
11. 数据类型：Oracle和PostgreSQL的数据类型有所不同，例如Oracle中的NUMBER类型在PostgreSQL中对应为NUMERIC类型。在进行数据迁移时，需要确保数据类型的映射正确
12. Substrb(string, 0, #{sjlbbmlength}) in (#{string1}) → SUBSTRING(string FROM 1 FOR #{stringlength}) IN (#{string1})
13. 使用ROW_NUMBER()函数来生成查询结果的序号列

SELECT ROW_NUMBER() OVER (ORDER BY column_name) AS row_num, column1, column2, ...
FROM table_name;

14. Mybatis标签 oracle返回map时字段是大写，pgsql是小写
15. 查询序列 SEQ_BGPT_SBSPSJB_ID.NEXTVAL → nextval(‘SEQ_BGPT_SBSPSJB_ID’)

四、说明

• pgsql序列都是重新创建的，部分表已存在数据，自增时会有主键重复的问题，遇到时修改序列当前值即可。
• 以上只针对我遇到的问题，还有没返现语法区别或问题，请大家补充一下。