Features I'd Like in PostgreSQL

Re:Features I'd Like in PostgreSQL by gregwebs on Sunday January 29, 2023 @08:08AM (#34567744)

Maybe you could use the technique of maintaining a separate adjacency list column and materializing the ordering list. It would be a simplified version of what is done for hierarchy to materialize a nested set: https://www.sqlservercentral.com/articles/hierarchies-on-ste...

A DB type could be nice to hide away the adjacency list.

The other suggestion here to use fractional numbers is similar to materialized path (ltree data type in Postgres) and has been formalized to apply to hierarchies with Farey Fractions: https://sigmodrecord.org/publications/sigmodRecord/0506/p47-...

[ Parent | on HN ]

Re:Features I'd Like in PostgreSQL by danielheath on Monday January 30, 2023 @12:00AM (#34575930)

If I were happy to rewrite the queries, I could use something like (forgetting the exact incantation)`select row_number(), rest_of_table as position partition by playlist_id order by position` to get the position column.

[ Parent | on HN ]