5 ms·

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?

by lysium 14y ago

pvh 14y ago
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.
- ibejoeb 14y ago
  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).
  - jacques_chester 14y ago
    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.
    - jfb 14y ago
      "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.
  - fusiongyro 14y ago
    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.
    - mercurial 14y ago
      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-developer/overview/index.html http://www.oracle.com/technetwork/developer-tools/sql-develo...
      - mickeyp 14y ago
        Or if you're an Emacs user you can use sqlplus.el, a very nice wrapper around SQL*Plus.
      - fusiongyro 14y ago
        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).
- e12e 14y ago
  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 :-)
knightni 14y ago
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.

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?

by lysium 14y ago

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?

pvh 14y ago

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.

ibejoeb 14y ago
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).
- jacques_chester 14y ago
  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.
  - jfb 14y ago
    "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.
- fusiongyro 14y ago
  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.
  - mercurial 14y ago
    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-developer/overview/index.html http://www.oracle.com/technetwork/developer-tools/sql-develo...
    - mickeyp 14y ago
      Or if you're an Emacs user you can use sqlplus.el, a very nice wrapper around SQL*Plus.
    - fusiongyro 14y ago
      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).
e12e 14y ago
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 :-)

knightni 14y ago

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.