#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
- start with 决定在哪个节点开始（决定哪个节点是根节点）
- prior 决定了上下查询，如果prior指向子节点则向下查询，如果指向父节点则向上查询
- 具体参考初步认识、常见问题、细节参考
- 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 =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=
)select * from CTE;
Oracle:to_char
- oracle的to_char(col,fmt)支持fmt格式化字符串可以为空，用于类型的转换
- pgsql的to_char(col,fmt)，不支持fmt为空，使用类型转换时可以使用cast()或string::text的方法，具体可以参考这里
- 把日期转换成当天的秒数=>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-15select 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( = )
when matched then
update set
name = ,
age = t1.age
when not matched then
insert (id,name,age)
values(,,t1.age)
Pgsql: with upsert as(
update stu t1
set name = ,
age = t2.age
from (id,name,age) t2
where =
returning ,,t1.age
)
insert into stu (id,name,age)
values(id,name,age)
where not exists(
SELECT 1
FROM upsert)
