Oracle数据库迁移至PGSQL，部分语法转换

#Oracle转PGSQL

Oracle:dual表

• 由于oracle不支持select ‘x’ 这类型的sql语句，只能使用select ‘x’ from dual;
• pgsql支持select 'x’这类型的sql语句

---

Oracle:NVL对应coalesce

• oracle的NVL(col,0)如果第一个参数是null，则返回参数2，如果第一个参数是非null，则返回参数1，参数1和参数2类型可以不一致
• pgsql的COALESCE(EXPR1,EXPR2,EXPR3…EXPRn)总体思路和nvl差不多就是判断多个参数，区别是各参数的类型必须保持一致

---

Oracle:connect by对应with recursive(递归查询)

• oracle的start with … connect by …prior

  1. start with 决定在哪个节点开始（决定哪个节点是根节点）
  2. prior 决定了上下查询，如果prior指向子节点则向下查询，如果指向父节点则向上查询
  3. 具体参考初步认识、常见问题、细节参考

• pgsql的with recusive,如下：

• 建表

create table My_test(
id integer,
fatherId integer,
name varchar
);
insert into my_test(id, fatherId, name) values
(‘1’,‘0’,‘A’),
(‘2’,‘1’,‘B’),
(‘3’,‘1’,‘C’),
(‘4’,‘2’,‘D’),
(‘5’,‘2’,‘E’),
(‘6’,‘3’,‘F’),
(‘7’,‘4’,‘G’),
(‘8’,‘2’,‘H’),
(‘9’,‘6’,‘I’),
(‘10’,‘8’,‘J’),
(‘11’,‘2’,‘K’),
(‘12’,‘9’,‘L’);

• CTE表若使用c.faterId则向上递归查询

WITH RECURSIVE CTE as(
select x.* from my_test x where id=‘10’
union all
select y.* from my_test y join CTE c on y.id=c.fatherId
)select * from CTE;

• CTE表若使用c.faterId则向下递归查询

WITH RECURSIVE CTE as(
select x.* from my_test x where id=‘2’
union all
select y.* from my_test y join CTE c on y.fatherId=c.id
)select * from CTE;

---

Oracle:to_char

• oracle的to_char(col,fmt)支持fmt格式化字符串可以为空，用于类型的转换
• pgsql的to_char(col,fmt)，不支持fmt为空，使用类型转换时可以使用cast()或string::text的方法，具体可以参考这里
  1. 把日期转换成当天的秒数=>to_char(timestamp,‘SSSSS’)

---

Oracle:MONTHS_BETWEEN

• oracle的months_between(date1,date2)函数返回亮哥日期之间的月份数、详细见
• 在pgsql中没有months_between函数，可以通过其他的sql实现，如下：

SELECT
date_part (‘year’, f) * 12
+ date_part (‘month’, f)+1
FROM age (date1,date2) f;

---

Oracle:to_date

• oracle的to_date(char,fmt)，在fmt中mi代表分钟，原因是sql中不区分大小写导致MM和mm被认为是相同的格式代码，所以使用mi替代mm，另外要以24小时制显示要用HH24，如下：

to_date(sysdate,‘yyyy-MM-dd HH24:mi:ss’) //mi是分钟
to_date(sysdate,‘yyyy-MM-dd HH24:mm:ss’) from dual;//mm会显示月份 oracle中的to_date参数含义

• pgsql的to_date(text,fmt),fmt定义基本与orcale一致，需要注意的是pgsql的to_date只能返回年月日，就算fmt定义了时分秒也只能返回年月日，如果需要返回时分秒则需要使用到to_timestamp(text,fmt),使用to_date可能会由于自动的隐式转换导致数据缺失,具体情况点击这里

select to_date('2019-01-15 18:33:41','yyyy-MM-dd hh24:mi:ss');//2019-01-15
select to_timestamp('2019-01-15 18:33:41','yyyy-MM-dd hh24:mi:ss')//2019-01-15 18:33:41+00

---

Oracle:date类型转到pgsql

• oracle的date类型存储时分秒年月日
• pgsql的date类型只存储年月日，如果需要把年月日都存储进来的话，需要使用到timestamp类型,不过timestamp类型默认是存储时分秒年月日时区还有高精度的日期数据，如果需要把时区去掉可以使用timestamp without time zone来去掉时区，如果需要把高精度去掉可以使用timestamp(0)来去掉小数点,都去掉的话就使用timestamp(0) without time zone

---

Oracle:Floor(x)取最大整数

• oracle的floor(x)可以传入参数x(小数)，返回最大的整数比如传入123.4返回123
• pgsql的trunc(x,Integer)可以传入参数x(小数),第二个参数代表小数的个数，比如零代表取整:

select trunc(123.4, 1);
– 123.4 正数1表示小数点的个数
select trunc(123.4, -2);
– 100
select trunc(123.4, -3);
– 0 -3表示最小值从1000算起,不足1000的则为零

Oracle：序列sql

• Oracle:->见此
• PgSQL:->见此

Oracle：DECODE

• Oracle: 两种语法格式，其他用法,如行列转换，分数分段

1.decode(expression,value,result1,result2)
如果expression=value，则输出result1，否则输出result2
2.decode(expression,value1,result1,value2,result2,value3,result3…,default)
如果expression=value1，则输出result1，expression=value2，输出reslut2，expression=value3，输出result3,若expression不等于所列出的所有value，则输出为default

Oracle: Merge

• Merge into table using() on(条件) when matched then update set xxx when not matched then insert xxx，详细参考: 用来合并UPDATE和INSERT语句。
  通过MERGE语句,这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE
• 转换为PGSQL时需要使用with upsert as(update table t1 set xx from xx where xx returning t1.xx):可以返回修改的数据，然后在使用insert into table(xx) xx where not exists(select 1 from upsert)

具体例子

Oracle: merge into stu using(id,name,age) t1
on(stu.id = t1.id)
when matched then
update set
name = t1.name,
age = t1.age
when not matched then
insert (id,name,age)
values(t1.id,t1.name,t1.age)
Pgsql: with upsert as(
update stu t1
set name = t2.name,
age = t2.age
from (id,name,age) t2
where t1.id = t2.id
returning t1.id,t1.name,t1.age
)
insert into stu (id,name,age)
values(id,name,age)
where not exists(
SELECT 1
FROM upsert)