Forum OpenACS Q&A: does postgresql treat SQL queries different from Oracle?
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?
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)
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 "table.foo" 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!
Hope this helps...
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...