Oracle转PostgreSQL
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 |
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)
