Oracle转PostgreSQL

最近在做一些Oracle SQL转PostgreSQL的工作，顺便记录这些改变，以便以后再转换有个参考。

描述	Oracle	PostgreSQL
分页	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	<driverClassName>oracle.jdbc.OracleDriver</driverClassName> <url>jdbc:oracle:thin:@192.168.1.1:1521:testdb</url>	<driverClassName>org.postgresql.Driver</driverClassName> <url>jdbc:postgresql://192.168.1.1:5432/testdb</url>
序列	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)