Oracle转PostgreSQL

最新推荐文章于 2026-05-27 12:38:34 发布

原创最新推荐文章于 2026-05-27 12:38:34 发布 · 1.5k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#postgresql #oracle #sql #数据库

Oracle 专栏收录该内容

13 篇文章

订阅专栏

本文记录了将Oracle SQL转换为PostgreSQL的过程，包括分页查询、日期处理、数据库连接配置、序列操作、别名使用、行处理、NULL值处理、分组查询和连接查询等方面的差异。通过实例详细展示了如何在两个数据库系统间进行有效迁移。

最近在做一些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)