News for hackers. Stuff that matters.
Re:Postgres 15 improves UNIQUE and NULL by barrkel on Monday July 11, 2022 @06:34AM (#32054372)
When I store tree-structured data in a relational database I generally add a 'path' column which is a denormalized string containing the names of all the parents with a path separator between.

The biggest reason is it makes finding all descendants very fast with a prefix search (foo/bar/%) on the path column when it's indexed. It's not unusual to want to find or update all descendants because descendants are usually related. If you don't have a path column, then you need to write a recursive CTE, which is Slow, or recurse in your application, which is normally even slower. The reason they're slow is they require a number of seeks which is exponential in the depth of the tree.

It also makes lookup of a node from the path fast, and producing a qualified path to a node fast, but these costs are linear in path length.

Anyway, this path column is also a good place to put your unique constraint.

If you don't want to restrict names from containing the path separator, you can escape application-side. For example, if using '/' as a path separator, consider '::' to escape ':' and ':s' to escape '/' - don't use your path separator in the escape or it'll muck up prefix searches.

Re:Postgres 15 improves UNIQUE and NULL by SnowHill9902 on Monday July 11, 2022 @08:30AM (#32055262)
Doesn’t feel natural in Postgres. I’d check an auxiliary table nodes_nodes (integer, integer) having an index of all ancestor-descendants.
Re:Postgres 15 improves UNIQUE and NULL by evv on Monday July 11, 2022 @04:25PM (#32061327)
Agree its a bit unnatural, but it seems workable. My problem with the auxiliary table is, I wouldn't know how to implement the unique constraint.
Re:Postgres 15 improves UNIQUE and NULL by SnowHill9902 on Monday July 11, 2022 @05:26PM (#32062109)
In your current implementation you either have no unique constraint or it is a more particular case of UNIQUE(ancestor, descendant).