spring boot + mybatis项目从oracle数据库转为pgsql

第一步：先新建pgsql数据库

-- 新建数据库

CREATE DATABASE investabs;

-- 新建用户

CREATE USER investabs_dev WITH PASSWORD 'investabs_dev';

-- 给用户赋权限

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA investabs_dev TO investabs_dev;

连接上数据库：

 url: jdbc:postgresql://ip:port/investabs
 username: investabs_dev
 password: investabs_dev
 driver-class-name: org.postgresql.Driver
 validationQuery: select 1

第二步：查询源库Oracle表结构信息并在新库pgsql执行

-- 表结构及字段注释
select a.TABLE_NAME,
       c.COMMENTS,
       a.COLUMN_NAME,
       b.COMMENTS,
       a.DATA_TYPE,
       case
           when a.COLUMN_ID = 1 then ');\r create table IF NOT EXISTS ' || a.TABLE_NAME || '('
           else ',' end || a.COLUMN_NAME || ' ' ||
       case
           when a.DATA_TYPE = 'VARCHAR2' or a.DATA_TYPE = 'NVARCHAR2' then 'varchar' || '(' || a.CHAR_LENGTH || ')'
           when a.DATA_TYPE = 'DATE' then a.DATA_TYPE
           when a.DATA_TYPE = 'NUMBER' then 'numeric' || '(' || a.DATA_LENGTH || ',' || a.DATA_SCALE || ')'
           when a.DATA_TYPE = 'CHAR' then a.DATA_TYPE || '(' || a.CHAR_LENGTH || ')'
           when a.DATA_TYPE = 'TIMESTAMP(6)' then 'TIMESTAMP'
           when a.DATA_TYPE = 'BLOB' then 'text'
           end || case when a.NULLABLE = 'N' then ' not null ' else '' end
                                                                                                        建表sql,
       case
           when a.COLUMN_ID = 1 then 'COMMENT ON TABLE ' || a.TABLE_NAME || ' IS ''' || c.COMMENTS || ''';'
           else '' end ||
       'COMMENT ON column  ' || a.TABLE_NAME || '.' || a.COLUMN_NAME || ' IS ''' || b.COMMENTS || ''';' 表和字段注释

from user_tab_columns a
         left join user_col_comments b on a.TABLE_NAME = b.TABLE_NAME and a.COLUMN_NAME = b.COLUMN_NAME
         left join USER_TAB_COMMENTS c on a.TABLE_NAME = c.TABLE_NAME

order by a.TABLE_NAME, a.COLUMN_ID
;


-- 普通索引
select case
           when t.index_name is null then null
           else

               -- 普通索引
                   'CREATE INDEX ' || t.index_name || ' ON ' || a.TABLE_NAME || ' (' ||
                   listagg(t.COLUMN_NAME, ',') within group ( order by a.COLUMN_ID ) || ');' end
           索引sql

from user_tab_columns a
         left join user_col_comments b on a.TABLE_NAME = b.TABLE_NAME and a.COLUMN_NAME = b.COLUMN_NAME
         left join USER_TAB_COMMENTS c on a.TABLE_NAME = c.TABLE_NAME
         left join user_ind_columns t on t.TABLE_NAME = a.TABLE_NAME and t.COLUMN_NAME = a.COLUMN_NAME
         left join user_indexes i on t.index_name = i.index_name and t.table_name = i.table_name
where not exists(
        select cu.*
        from user_cons_columns cu,
             user_constraints au
        where cu.constraint_name = au.constraint_name
          and au.constraint_type = 'P'
          and au.table_name = a.TABLE_NAME
          and cu.COLUMN_NAME = b.COLUMN_NAME
    )
group by a.TABLE_NAME,
        -- a.COLUMN_NAME,
         t.index_name
order by a.TABLE_NAME
;


-- 主键sql
select
    -- 主键
    'ALTER TABLE ' || a.TABLE_NAME || ' ADD CONSTRAINT ' ||
    case
        when cu.CONSTRAINT_NAME = a.TABLE_NAME then cu.CONSTRAINT_NAME || '1'
        else cu.CONSTRAINT_NAME end
        || ' PRIMARY KEY (' || listagg(cu.COLUMN_NAME, ',') within group (
        order by a.COLUMN_ID ) || ');'
        主键sql

from user_tab_columns a
         left join user_col_comments b on a.TABLE_NAME = b.TABLE_NAME and a.COLUMN_NAME = b.COLUMN_NAME
         left join user_cons_columns cu on cu.TABLE_NAME = a.TABLE_NAME and cu.COLUMN_NAME = a.COLUMN_NAME
         left join user_constraints au on cu.constraint_name = au.constraint_name and au.table_name = a.TABLE_NAME
where cu.COLUMN_NAME is not null
  and au.constraint_type = 'P'
group by a.TABLE_NAME, cu.CONSTRAINT_NAME
order by a.TABLE_NAME
;
/**********************/
需要在此处先执行数据脚本（可使用navicatedaochu insert sql数据）
/**********************/
-- 外键sql
select
-- 主键
'ALTER TABLE ' || a.TABLE_NAME || ' ADD constraint ' ||
case
    when cu.CONSTRAINT_NAME = a.TABLE_NAME then cu.CONSTRAINT_NAME || '1'
    else cu.CONSTRAINT_NAME end
    || ' foreign KEY (' || listagg(cu.COLUMN_NAME, ',') within group (
    order by a.COLUMN_NAME ) || ') references ' || t.TABLE_NAME || '(' ||
listagg(t.COLUMN_NAME, ',') within group ( order by t.COLUMN_NAME ) || ');'
    外键sql
-- cu.*,au.*,

from user_tab_columns a
         left join user_col_comments b on a.TABLE_NAME = b.TABLE_NAME and a.COLUMN_NAME = b.COLUMN_NAME
         left join user_cons_columns cu on cu.TABLE_NAME = a.TABLE_NAME and cu.COLUMN_NAME = a.COLUMN_NAME
         left join user_constraints au on cu.constraint_name = au.constraint_name and au.table_name = a.TABLE_NAME
         left join user_constraints s on au.R_CONSTRAINT_NAME = s.CONSTRAINT_NAME
         left join user_cons_columns t on t.constraint_name = s.constraint_name and t.COLUMN_NAME = cu.COLUMN_NAME
where cu.COLUMN_NAME is not null
  and au.constraint_type = 'R'
group by a.TABLE_NAME, cu.CONSTRAINT_NAME, t.TABLE_NAME
order by a.TABLE_NAME
;
-- 序列
select 'create sequence ' || a.SEQUENCE_NAME || ' increment ' || a.INCREMENT_BY || ' minvalue ' || a.MIN_VALUE ||
       ' maxvalue ' || a.MAX_VALUE || ' start ' || a.LAST_NUMBER  || ' cache ' || a.CACHE_SIZE || ' CYCLE ;'
from dba_sequences a
where sequence_owner = 'INVESTABS_DEV';

第三步：修改代码中的函数及sql结构

基本数据类型差异

序号	Postgresql	存储长度	描述	Oracle对应
01	varchar	有限制	变长字符串	varchar2
02	char	固定	定长字符串	Char
03	text	无限制	变长字符串	Clob
04	Date		不含时间	Date
05	Time		不含日期
06	Timestamp		日期+时间
07	smallint	2 bytes	[-32768,32767]	Number
08	integer	4 bytes	整数类型
09	bigint	8 bytes	大范围数据类型
10	decimal/numeric	可变	指定精度,带小数位
11	real	4 bytes	变长，不精确
12	double precision	8 bytes	变长，不精确

基本函数差异

序号	Item	Postgresql	Oracle对应
01	字符串连接符	Concat()
02	判空操作	coalesce()	nvl()
03	dual伪表	不支持(查询常量不需要加from)	支持
04	Trunc(时间)	date_trunc()	trunc(date)
05	取当前系统时间	current_time, now()…	SYSDATE
06	to_char, to_number	需指定格式	自动格式转换
07	decode	× 需改写成case when	√
08	outer join	× left(right) join	√
09	GOTO	× 需改写成function	√
10	procedure	× 需改写成function	√
11	package	× 需改写成function	√
12	返回左边表中的数据，返回的是差集	EXCEPT	MINUS

其他需要转换的地方：

1、pgsql没有dual

类似sql：

SELECT to_char(sysdate,'YYYYMMDD')|| lpad(SEQ_COMM_6.NEXTVAL,6,'0') FROM DUAL

改为：

SELECT to_char(now(),'YYYYMMDD')|| lpad(cast(nextval('SEQ_COMM_6') as VARCHAR),6,'0')