Hacker News Reader

Comment by robconery | Hacker News Reader

> our data which is partitioned by week then organized according to a hierarchical triangular mesh with bitmapped indexes

Sounds like you're getting your money's worth out of Oracle - which is a good thing. For many others (especially those in the .NET world) they use very few of the high end SQL Server stuff.

View on HN

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.

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.

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.

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.

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

Reply on news.ycombinator.com