PortletPaul
asked on
Oracle to Postgres Select Query differences
Looking for learnings/advice of Oracle to Postgres conversion (SELECT queries in particular).
I'm aware of a few key differences (e.g. no "dual", no "sysdate", no "connect by ...") but I'm hoping someone has, or knows of, a good fact sheet of differences.
If it helps: Oracle 11 to "recent Postgres" (not sure of version as yet)
I'm aware of a few key differences (e.g. no "dual", no "sysdate", no "connect by ...") but I'm hoping someone has, or knows of, a good fact sheet of differences.
If it helps: Oracle 11 to "recent Postgres" (not sure of version as yet)
ASKER
I was hoping for some tips so I could some initial scanning of code
aware of the dual workaround
yep. cte instead of connect by (don't think I have any left anyway)
Will try current_date ... good idea.
no common function changes?
I guess decode is also a no go - easy enough to replace
any comments on performance?
aware of the dual workaround
yep. cte instead of connect by (don't think I have any left anyway)
Will try current_date ... good idea.
no common function changes?
I guess decode is also a no go - easy enough to replace
any comments on performance?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I've been working towards an Oracle 12.2 to Postgres 11 (likely 12 by the time we get real serious) migration for a few months now. Yes, getting used to the differences has been interesting.
I've not found a nice cheat sheet but I've also not really had the need to look. When I need to do something, Google is pretty quick to offer advice.
I also prefer the Postgres documentation over Oracle's. I find it MUCH easier to find what I'm looking for in their docs.
Haven't started with any PL/SQL/stored code migrations yet. From the few pg/sql code blocks with simple loops that I'm copied, it should be interesting. Had to drop ALL tables in a schema. Code is similar to PL/SQL but different enough.
>>"dual"
I don't miss it. If you do, easy enough to create it.
>>"sysdate"
But both have current_date and current_timestamp (not sure about Oracle 11 and I'm too lazy to pull up the 11g docs).
>>"connect by ..."
Pretty sure you can do the same with with recursive CTEs which both have. Not tried them out yet!