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)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Want me to blow your mind? If you disable the autocommit, which is default, DDL can be rolled back!
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!
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!
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?
>>no common function changes?
>>any comments on performance?
Haven't gotten there yet. Been focused mainly on data migrations thus far.
Going through my notes I've been keeping. Thought I'd share...
Slight correction from a point I made above:
current_timestamp and now are the start time of the transaction not the system time.
clock_timestamp() is system date.
Some points of interest I've made notes about:
Most things available come from extensions and modules.
You can see what is there with:
select * from pg_available_extensions;
select * from pg_available_extension_ver
There is a community site for folks to post their own (use at your own risk since it outside main Postgres):
https://pgxn.org/
Related to tuning but I haven't been through this yet. Just made a note to read up on it later:
https://www.postgresql.org/docs/12/pgstatstatements.html
Knowing the tabs will be messed up when posted, term differences that helped me:
Not sure why you would want to but you can create a table with no columns: create table bob();
Deleted and updated data remains and data files become fragmented. Important things to read up on: Transaction id wraparound and vacuum.
If running on a server and not as a service( RDS):
systemctl and pg_ctl can become disconnected and one may not have the most accurate state.
For example: use systemctl to start and pg_ctl to stop. systemctl can still think some things are running.
Solution: Pick a tool and stick with it. Don't mix and match.
>>any comments on performance?
Haven't gotten there yet. Been focused mainly on data migrations thus far.
Going through my notes I've been keeping. Thought I'd share...
Slight correction from a point I made above:
current_timestamp and now are the start time of the transaction not the system time.
clock_timestamp() is system date.
Some points of interest I've made notes about:
Most things available come from extensions and modules.
You can see what is there with:
select * from pg_available_extensions;
select * from pg_available_extension_ver
There is a community site for folks to post their own (use at your own risk since it outside main Postgres):
https://pgxn.org/
Related to tuning but I haven't been through this yet. Just made a note to read up on it later:
https://www.postgresql.org/docs/12/pgstatstatements.html
Knowing the tabs will be messed up when posted, term differences that helped me:
Industry Term PostgreSQL Term
Table or Index Relation
Row Tuple
Column Attribute
Data Block Page (when block is on disk)
Page Buffer (when block is in memory)
Not sure why you would want to but you can create a table with no columns: create table bob();
Deleted and updated data remains and data files become fragmented. Important things to read up on: Transaction id wraparound and vacuum.
If running on a server and not as a service( RDS):
systemctl and pg_ctl can become disconnected and one may not have the most accurate state.
For example: use systemctl to start and pg_ctl to stop. systemctl can still think some things are running.
Solution: Pick a tool and stick with it. Don't mix and match.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial