Hacker News new | ask | show | jobs
by drunkpotato 4671 days ago
How do hierarchical tables compare to Postgres' hstore?

I think recursive structures like lists and trees are one weakness of the relational model, and haven't found a fully satisfying relational answer to the limitations. It seems to me like the assumption of atomic column types is a major weakness inherent in the relational model itself when it comes to recursion. Any thoughts/comments?
2 comments
jacques_chester 4671 days ago
> How do hierarchical tables compare to Postgres' hstore?

If I am reading them correctly, it's a storage strategy, not a specific "feature" per se. The closest analogy to hstore is that they provide native support for storing and querying protobuf blobs.

> I think recursive structures like lists and trees are one weakness of the relational model, and haven't found a fully satisfying relational answer to the limitations.

It depends on why you're using trees or graphs.

If it's inherent in the data, then modern SQL has recursive queries that make it much easier than the old methods.

If it's inherent in the model, you will find it harder. You might need to pick a non-standard approach, such as PostgreSQL's inherited tables. I'd think long and hard before saying it's inherent in the model, by the way. Strictly speaking you can represent the same thing as sets of relations or as a graph; it's better to utilise the strengths of the tool in front of you.

link
zapov 4671 days ago
Both Oracle and Postgres have objects and collections. This allows you to build hierarchical structures. Postgres unfortunately doesn't allow for recursive structures ;(

If you are interested in how to do it take a look here: https://blog.dsl-platform.com/postgres-bridge-between-worlds...

link
jacques_chester 4671 days ago
It should be noted that Oracle's object types are a feature of PL/SQL, their imperative programming language.

PostgreSQL's concept of object-relational databases is baked into the SQL. Into the logical model of the data itself.

Put another way: Oracle doesn't have table inheritance.

link
zapov 4671 days ago
We are using Oracle object types in plain SQL, so no, they are not PL/SQL exclusive. But yeah, you can do a lot less with them outside of PL/SQL.
link
jacques_chester 4671 days ago
Right, you're right, I overlooked CREATE TABLE OF.
link