Re: Writing oracle/postgress generic SQL

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(at)gmail(dot)com>
Sent by: pgsql-general-owner(at)postgresql(dot)org
02/23/2007 01:27 AM

To
pgsql-general(at)postgresql(dot)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
----------------------------------------------------------------------------------------------