oracle库迁移Postgres库

最近项目上要求oracle数据库迁移到pg库上去，这是本人在迁移过程中遇到的问题，记录一下，可能不全，没有的可以去pg官网查看或寻找，如果有问题还望大家多多指正。

Postgres库中文官网地址

1.日期截取 trunc()

• oracle — trunc()

• Postgres — date_trunc(field,source [time_zone ])

  field 的有效值(常用的)为year,month,day,hour,minute,week(截取到这周的周一)。

  source指的是类型timestamp,timestamp with time zone,interval

--oracle
select trunc(sysdate,'mm') from dual;
--对应pg
select date_trunc('month',now()::TIMESTAMP(0));

--oracle
select trunc(sysdate) from dual;
--对应pg
select date_trunc('day',now()::TIMESTAMP(0));

--pg库其他举例
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
Result：2001-01-01 00:00:00
SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
Result: 2001-02-16 00:00:00-05
SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
Result: 3 days 02:00:00

如果仅是截取数字，用法跟oracle一样

2. to_date()

--oracle
select to_date('2023-04-25 23:59:59','yyyy-mm-dd hh24:mi:ss') data from dual;
Result:2023/4/25 23:59:59
--pg
select to_date('2023-04-25 23:59:59','yyyy-mm-dd hh24:mi:ss')
Result:2023/4/25  

oracle的to_date()不等价于pg库的to_date()，在PostgreSQL 中 to_date()虽然是将字符串转换成日期， 但是 仅仅是年月日部分

oracle的to_date()等价于 pg库的to_timestamp() (出来后会带着时区，要想去除时区可以在后边加上 to_timestamp()::TIMESTAMP(0) )

3.substr() 截取

• oracle

  oracle的substr()函数下标截取从0和1开始都行

• pg

  pg的substr()函数下标截取只能从1开始，如果是0的话，相当于从-1开始。

--oracle
select substr('123456789',0,1) from dual;
Result:1
select substr('123456789',1,1) from dual;
Result:1
    
-pg
select substr('123456789',1,1);
Result:1   

4.当前时间 sysdate

--oracle
select sysdate from dual;
--pg 格式化时区
select now()::TIMESTAMP(0);
--或者 不格式化时区
select now()/current_timestamp;

--其他
--pg 不带时分秒的当前日期
select current_date;

5. decode()

oracle中decode函数，在pg中用case when … then … else … end 代替。

用法与oracle的case when 一样。

6.nvl()

oracle 中的nvl()等价于pg库中的coalesce()。

7. update 更新操作

--pg库中update操作set 后边的字段不能加表的别名
update 表名 a set a.字段 = 'test' where a.字段 = 'test';
--上述这样修改是错的，set后边不允许加表的别名，where后边可以。改成下边这样可以
update 表名 a set 字段 = 'test' where a.字段 = 'test';

8.oracle->pg库字段对比

• varchar2() -> varchar()
• number -> numeric/int
• clob -> text/bytea
• blob -> bytea
• date -> timestamp

pg库中date格式不带时分秒

9.递归

oracle 中的递归一般是用start with … connect by prior … 或者用 with 递归。

pg库中的递归用 with RECURSIVE ,用法与oracle的with递归一样

--举例
create table t_test 
(
    num numeric,
    p_num numeric
);
insert into t_test(num,p_num) values(1,0);
insert into t_test(num,p_num) values(2,1);
insert into t_test(num,p_num) values(3,2);
insert into t_test(num,p_num) values(4,3);
with RECURSIVE tb_a(num,p_num) as
(
select num,p_num from t_test where p_num ='0'
union all
select b.num,b.p_num from t_test b,tb_a a where a.num =b.p_num
)
select num from tb_a;

递归的相关用法

oracle中的伪列 level

--pg库中level如何实现
with RECURSIVE tb_a(num,p_num) as
(
select num,p_num,1 as lv from t_test where p_num ='0'
union all
select b.num,b.p_num,a.lv+1 as lv from t_test b,tb_a a where a.num =b.p_num
)
select num,lv from tb_a;

connect_by_isleaf 判断是否为叶子节点

--pg库中connect_by_isleaf 如何实现
with RECURSIVE tb_a(num,p_num) as
(
select num,p_num from t_test where p_num ='0'
union all
select b.num,b.p_num from t_test b,tb_a a where a.num =b.p_num
)
select a.num,a.p_num, 
case when b.num is null then '1' else '0' end is_leaf 
from tb_a a left join tb_a b on a.num =b.p_num;

10. dual

oracle 中有dual表，但是在pg库中没有

--oracle
select sysdate from dual;
--pg 
select now();

11. instr() 模糊查询

oracle instr() -> pg STRPOS()

括号里的用法一致，不需要变换

12. 日期相减或者相加

oracle 中的日期可以直接相加或者相减，但是pg库中是不可以的

pg库中两个日期字段相减或者两个日期相减可以用 EXTRACT(EPOCH FROM ...)把每个值都转换成秒数，然后执行减法， 这样会得到两个值之间的***秒***数。

SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
       EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
Result: 10537200
    
--拓展 EXTRACT
--获取日期当中（月份）里的日域（1-31）；对于interval值，是日数
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：16
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
结果：40    
--一年中第几天
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
结果：47
--一周中的日，从周日（0）到周六（6）
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
结果：5

--oracle 可以直接相加减 默认 1 是 1天
select sysdate +1 from dual;
--pg
select now()::TIMESTAMP(0) + INTERVAL '1 day';
--常用的还有 month,year,hour,minute等
select now()::TIMESTAMP(0) + INTERVAL '1 minute';

13.正则表达式

13.1 拆分提取

--oracle regexp_substr() 拆分一般与 regexp_count() 搭配，具体使用就不介绍了
select regexp_substr('test,test1','[^,]+',1,level)  from dual connect by level <= regexp_count('test,test1','[^,]+');
结果: 
test
test1
--pg 相比较oracle而言简单一点，实现效果一样
select regexp_split_to_table('test,test1',',');

13.2 替换

oracle 和pg 都是 regexp_replace()，但是用法不一样，这个网上可以查到。

14.listagg() 拼接函数 与group by 搭配

pg库用string_agg()

create table t_test 
(
    num numeric,
    name varchar(255)
);
insert into t_test(num,name) values(1,'你');
insert into t_test(num,name) values(1,'好');
insert into t_test(num,name) values(1,'啊');
select num,string_agg(name,',') from t_test group by num;
结果:
1 你,好,啊
--注意：其中，name字段必须是varchar类型，数字类型是不能拼接的。