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 "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!
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...
