I've converted stuff from PostgreSQL to Oracle before, and some of the biggest pains were "OFFSET ... LIMIT ..." in PostgreSQL vs. ROWNUM or ROW_NUMBER in Oracle (depending on version of Oracle, including having to wrap the query with ROWNUM/ROW_NUMBER in a subselect - I greatly prefer OFFSET and LIMIT, especially for web applications), and sequence NEXTVAL syntax. There may be some date type conversion / formatting issues, too. Temporary tables were somewhat different, too, as I recall.
You might be able to hide some of the internal differences by creating database-specific views and functions, and using simpler queries from the views at a higher level of the application. Of course, there is that weird Oracle thing where you have to say "SELECT .... from DUAL" instead of just "SELECT xxx" to get simple function return values.
Some of the Oracle stuff may differ, depending on version.
Susan Cassidy
"Ben Edwards" <funkytwig@gmail.com> Sent by: pgsql-general-owner@postgresql.org
02/23/2007 01:27 AM
To
pgsql-general@postgresql.org
cc
Subject
[GENERAL] Writing oracle/postgress generic SQL
Anyone know of any guidelines for writing SQL which works under Oracle witch will also work under postgress. This is to ensure that SQL written for an Oracle database can be migrated to postgress later.
Ben -- Ben Edwards - Brussels, Belgium & Bristol, UK If you have a problem emailing me use http://www.gurtlush.org.uk/profiles.php?uid=4 (email address this email is sent from may be defunct)
---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
---------------------------------------------------------------------------------------------- Simply protected storage solutions ensure that your information is automatically safe, readily available and always there, visit us at http://www.overlandstorage.com ----------------------------------------------------------------------------------------------