News for hackers. Stuff that matters.
Re:Postgres: The Bits You Haven't Found by lysium on Saturday March 02, 2013 @03:40PM (#5310934)
Interesting read! I assume, this is all not portable? Or is there similar functionality for other DB, say MySQL (not that I am aware of) or Oracle?
Re:Postgres: The Bits You Haven't Found by pvh on Saturday March 02, 2013 @06:24PM (#5311583)
Some of it is in the standard, some of it is PG specific. MySQL is pretty terrible and doesn't support any of it that I'm aware of. I expect Oracle has their own versions of much of this stuff, but I've made it a hobby in my life to not learn Oracle.
Re:Postgres: The Bits You Haven't Found by ibejoeb on Saturday March 02, 2013 @07:55PM (#5311957)
Oracle has subquery factoring (the "with" clause) and recursion; native arrays, hash maps, collections, nested tables, and other complex data types; remote database links; queues (similar to listen/notify); regex querying; attribute-level timezone support; extremely powerful window functions; text search; tons of built-in crypto (if you really insist); and most of the related things on this list.

The biggest killers (vs. postgres): no simple helpers (generate_series, for example, is achievable with simple, efficient recursion, but you have to write the whole statement); a truly antiquated client program (sqlplus); built-in json, but you do have associative arrays/hash maps and nested tables, so you can achieve exactly the same thing, and it's indexable*; and range types (meh imo).

Re:Postgres: The Bits You Haven't Found by jacques_chester on Saturday March 02, 2013 @10:25PM (#5312366)
I think I've had Oracle blow up on multiple AS statements, so that's one place where Postgres would come out well in front.

Basically WITH-AS lets you declared temporary named subqueries. That is, a pretty stock standard programming technique -- give a name to a fiddly expression to simplify subsequent code. Very useful.

Oracle has lots of annoyances though. No SERIAL or IDENTITY type. Having to write the same boilerplate sequence/insert trigger/update trigger over and over and over is very tedious and a recipe for subtle errors when you forget one of them or mess it up.

No boolean type in SQL. I don't even know what to say about that.

Re:Postgres: The Bits You Haven't Found by jfb on Sunday March 03, 2013 @09:28AM (#5313820)
"No boolean type in SQL. I don't even know what to say about that."

Tom ties himself into unbelievable knots trying to defend this. It's the purest form of technical Stockholm Syndrome I've ever seen from a putatively intelligent person.

Re:Postgres: The Bits You Haven't Found by fusiongyro on Sunday March 03, 2013 @12:20AM (#5312655)
It's worth noting that using some of these features bump you up into a different pricing tier. And last time I checked the usual way to achieve a recursive query was with the non-standard "CONNECT BY" syntax. I have found rlwrap does a lot to mitigate the pain of sqlplus, but I still use it (and Oracle) as little as I can possibly get away with.
Re:Postgres: The Bits You Haven't Found by mercurial on Sunday March 03, 2013 @09:49AM (#5313886)
Even with rlwrap, sqlplus is awful. However, you can try SQL developer, which is fairly decent GUI frontend:

http://www.oracle.com/technetwork/developer-tools/sql-develo...

Re:Postgres: The Bits You Haven't Found by fusiongyro on Monday March 04, 2013 @11:17AM (#5318876)
I actually wind up using both. But SQL Developer is pretty heavy and I prefer using psql to a GUI tool with Postgres, so I wind up in sqlplus a lot (when dealing with Oracle, which is seldom).
Re:Postgres: The Bits You Haven't Found by mickeyp on Sunday March 03, 2013 @10:09AM (#5313958)
Or if you're an Emacs user you can use sqlplus.el, a very nice wrapper around SQL*Plus.
Re:Postgres: The Bits You Haven't Found by e12e on Saturday March 02, 2013 @11:49PM (#5312576)
I'm not very familiar with it, but the big Free SQL Server that everyone seems to keep forgetting, Firebird, appears to have at least some of these:

  http://www.firebirdsql.org/en/sql-conformance/
Whenever I think of Firebird, i get a little bit of a bad conscience because I've never really played with it :-)
Re:Postgres: The Bits You Haven't Found by knightni on Sunday March 03, 2013 @04:15AM (#5313084)
WITH and window functions are portable (and both awesome - they will change the way you write your SQL for the better). I'm not sure if WITH RECURSIVE is portable - my recollection is that oracle and db2 at least don't need/use the RECURSIVE qualifier for recursive ctes: they just work it out for you.

Time intervals are also in the SQL standard as I recall (although not in the format listed here - postgres supports standard syntax too though). Not sure how fully supported it is cross-database, though.