Forum OpenACS Q&A: Response to does postgresql treat SQL queries different from Oracle?

The best list of differences is probably in my head, or Ben's :)

Let's see - the worst thing you run up against is the lack of outer joins.  Unions are your friend.  Still a pain in the butt, though.

PG6.5 doesn't have to_char, PG7.0 fixes that but the beta has a DOA syntax error that you don't even want to hear about, wait until beta2 or pick up my snapshot at dsl-dhogaza.pacifier.net (anonymous ftp/pub directory).

PG6.5 doesn't have referential integrity, though it does recognize the  syntax.  PG7.0 does, including "on delete/update cascade/set default/set null/no action(i.e. give error)".  It doesn't pick up the type from the foreign key, though - I think that's an Oracle extension and PG stuck to the standard.  It's easy enough to add them to your table definitions.

Dates in general are different, as PG has the SQL92 standard "timestamp" (you can use its older "datetime" as a synonym).  "date" is YYYY-MM-DD only, so you want to remember that when moving stuff over.  I'd study the ACS port for date handling, if I were you, among other things you can be humored by the stumbling/mumbling path we took  as we tried out different ways of mapping Oracle date stuff to Postgres.  In some ways I like Postgres better, check out the ported ad-calendar-widget.tcl in the tcl library.

PL/SQL is quite reasonably shadowed by PL/pgSQL.  The latter is in some ways easier to use (no need for cursors, for instance), but is less powerful and indeed is just a thin language wrapper around the query engine.  Again, check our port for some real-world examples of what it is like to port PL/SQL to PL/pgSQL.  This has been the most pleasant surprise in the whole process, much easier to do than I'd thought at first.  The biggest problem is that the function interface, inherited from QUEL days (which I presume didn't have a NULL), can't handle passing NULLs into or out of functions.  You need to pick an empty value just as we've done throughout our lives in other programming languages.

The rest will follow in a second message, I just found a bug in our reintegration of my bboard port with ACS 3.x (lurking CLOB grr)