Oracle转PostgreSQL
Oracle postgresql oracle sql 数据库
最近在做一些Oracle SQL转PostgreSQL的工作，顺便记录这些改变，以便以后再转换有个参考。
描述OraclePostgreSQL
分页
select * from ( select z.*,rownum as num from( " + sql + " ) z ) where num>=" + startRow + " and num<=" + endRow
select z.* from ( " + sql + " ) z LIMIT " + pageSize + " OFFSET " + startRow
日期
① select sysdate from dual② select t.* from test_user t where sysdate < t.end_date +1③ select t.* from test_user t where now() < date_trunc('day',t.end_date) + interval '1d'④ select * from test_user where round(pwd_eff_date+pwd_eff_days-now())>0
① select now();② select t.* from test_user t where now() < date_trunc('day',t.end_date) + interval '1d'③ select trunc(date_trunc(‘day’,now()) - interval ‘60d’)④ select * from test_user where (pwd_eff_date + (interval '1d' * pwd_eff_days))::date - now()::date >0
DB
序列
test_seq_log.nextval
nextval(‘test_seq_log’)
别名
① update test_user t set t.test_username=‘123456’② select * from (select * from TEST_STAFF where staff_id = ? or employee_id = ?) where status=‘1’
① update test_user set test_username='123456’② select * from (select * from TEST_STAFF where staff_id = ? or employee_id = ?) result where status=‘1’
Row
select t.*, t.rowid from test_user t
select t.*, t.ctid from test_user t
日期
nvl
select DISTINCT * from test_feature t where nvl(t.parent_id,0)=0
select DISTINCT t.* from test_feature t where coalesce(t.parent_id,0)=0
分组
SELECT COUNT(*) FROM ET_CHOICEITEM WHERE QUE_ID = 60421 ORDER BY ITEM_ID
SELECT COUNT(*) FROM ET_CHOICEITEM WHERE QUE_ID = 60421 ORDER BY ITEM_ID
select sc.*,s.*,c.*,r.* from test_staffcourse sc,test_staff s,test_course c,test_result r where sc.training_id=s.training_id and sc.course_id=c.course_id and r.training_id(+)=sc.training_id and r.COURSE_ID(+)=sc.course_id
select sc.*,s.*,c.*,r.* from test_staffcourse sc join test_staff s on sc.training_id=s.training_id join test_course c on sc.course_id=c.course_id left join test_result r on r.training_id=sc.training_id and r.COURSE_ID=sc.course_id
connect by prior
select distinct * from TEST_FEATURE connect by prior PARENT_ID=FEATURE_ID start with FEATURE_ID in (11, 12) order by FEATURE_ID
select a.* from TEST_FEATURE a where a.FEATURE_ID in (11, 12) union all select distinct * from TEST_FEATURE b where b.FEATURE_ID in (select distinct c.parent_id from TEST_FEATURE c where c.FEATURE_ID in (11, 12)) order by FEATURE_ID
Decode
decode(sign(decode(sc.last_result,null,90000,0,9000,-1,90000,sc.last_result)-c.pass_mark),-1,0,1)=1)
case when sign(case when sc.last_result=null then 90000 when sc.last_result=0 then 9000 when sc.last_result=-1 then 90000 else sc.last_result end - c.pass_mark) = -1 then 0 else 1 end = 1)
Rownum
SELECT ROWNUM, empno, ename, job FROM emp WHERE ROWNUM < 5 ORDER BY ename;
select row_number() over () as rownum, * from emp limit 10 offset 5;
复制表结构：
create table test_user_backup_20210223 as
( select * from test_user)
参考阅读
发表评论