On: PostgreSQL Rising

[juiceandjuice]

Yeah, there's a lot of other things we use it for. We have a lot of hierarchical data that uses the CONNECT BY statement, and I hate it because it has issues with scaling and bad execution plans, so I'm working on migrating all of that to a closure table instead.

[va_coder → juiceandjuice]

Does Postgres have an equivalent Connect By feature? Last I checked it didn't. If you don't need to scale your hierarchical data much, its pretty handy. I'd prefer to go to Postgres too.

[einhverfr → va_coder]

Since way back when the tablefunc contrib module had a connectby() function, but it has some limitations being a C function.

The better way is to use WITH RECURSIVE CTE's. See http://ledgersmbdev.blogspot.com/2012/07/ctes-and-ledgersmb.... for how we use them in LedgerSMB for hierarchical data.

[rsutphin → va_coder]

PostgreSQL 8.4+ has recursive common table expressions. I don't know if they do everything that CONNECT BY does, but I know that they do everything that I've ever used CONNECT BY for.

[zzzeek → va_coder]

WITH RECURSIVE is the SQL standard version of what CONNECT BY provides with oracle. Postgresql and SQL Server both have it.