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

What problems would I encounter if I, say,
took a bunch of tcl scripts containing SQL written
for Oracle and ran them using PostgreSQL ?

I've heard people say there are differences between the two.
Where can I find a list of differences?

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 (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)

Part two ... damn, I ran across that clob the other day and meant to get rid of it ... oh well, I'll fix it in a minute ...

There are also SQL functions which might be useful at times, though thus far I've just stuck to mechanical translations of PL/SQL.

Oracle treats '' in a non-standard manner, at least for inserts and updates (haven't tested comparisions in select queries).  Despite Oracle's perverse implementation, '' is NOT the same as NULL, which is  defined to be a value belonging to no type.  '' is the empty string, just as 0 is zero, not NULL.  Grrr..."not null" constraints will point  these cases out to you quite quickly.

Postgres has Oracle-like sequences, but the syntax is different due to the fact that "" was already in use for inheritance.

Postgres has honest-to-god triggers, thankfully, but not the statement  variety, just "for each row".

Postgres has no CLOB/BLOB.  However, I've implemented a BLOB feature within the AOLserver Tcl API, so the ACS is in fat city in that regard.  If you're thinking standalone, though, and need BLOB's you're  kind of sunk.  There is a large object implementation but it sucks, big time.

Real large objects are on the way, though, later this year and they'll  be great - transparent.  No CLOB/BLOB bullshit, just big types with the mapping to a segmented table structure done automatically, with optional compression, too.

varchar limit is about 8100 with the standard build, but you can easily increase the blocksize to 16KB or 32KB, which lets you have varchars just slightly shorter than that.  Even better, PG7.0 has a new type lzText - compressed text which gets you about 2.5-3.5 for average strings, much more for highly redundant ones.  This type will disappear when real large objects arrive, but the implementor (same guy in each case) has pledged an invisible and automatic upgrade path when the time comes.  I can't wait to switch the ACS over to PG7.0 for  this reason alone!

That's my quick, off the top of the head summary.  Of course, Oracle can run circles around Postgres, which suffers from pathological brainlock in some cases, but not nearly as often as you might expect and folks are working on improving performance.  Indeed, HAVE worked on performance, for web applications in particular it has greatly speeded up (read-only simple selects in particular).  The good thing for us web hackers is that Postgres scales well in terms of users, as there is no table locking, just row locking for writers with readers never blocked (unless you lock a table yourself, of course).

Hope this helps...

Thanks for the highlights Don!  I'm wondering if the Oracle's "start with ... connect by" extension is implemented in Postgresql or anywhere in its radar screen?

That is a feature I found very useful (ie: can't live without) and I realize My-Sql guys have it on their TO-DO list...

No, connect-by isn't supported by Postgres nor is it currently on the "to-do" list.  It is very nice to have, but we've worked around it.  The work around is slower, of course, and painful but workable.