4 ms·

I actually solved this exact problem recently (who is NOT trying to recreate Roam Research amirite?) And surprised no one else suggested the solution I came up

I actually solved this exact problem recently (who is NOT trying to recreate Roam Research amirite?) And surprised no one else suggested the solution I came up with: just use a text column? When a new list is created the items get incrementing chars as text. If a user reorders an item, the items new sort order value is the value of the item above it concatenated to an "a". This is infinitely extensible, it's only slightly a drag on storage, and very trivial to clean up with some periodic code if storage is actually getting to e a problem. Works like a charm and Dont need to modify postgres itself for this!

yorwba 6y ago
> the items new sort order value is the value of the item above it concatenated to an "a" Say you insert three items. a: 1, b: 2, c: 3. Then you reorder 3 after 1. The new order is a: 1, aa: 3, b:2. Then you reorder 2 after 1. The new order is a: 1, aa: 2, aa: 3. But since there are now two values with the same sort key, it might as well be a: 1, aa: 3, aa: 2. Did I misunderstand your solution or is that a bug?
- deleted 6y ago
  [deleted]
- lukeramsden 6y ago
  Here is a StackOverflow Q&A about something just like this: https://stackoverflow.com/questions/38923376/return-a-new-string-that-sorts-between-two-given-strings https://stackoverflow.com/questions/38923376/return-a-new-st... A similar system is used in Jira called LexoRank
- ramraj07 6y ago
  I ran into this problem and instead of solving it the proper way I took the lazy route by adding a unique constraint on the column and when it threw an errror in the insert I just added one more extra letter!
  - n4r9 6y ago
    It's a neat idea, but this edge case makes it seem a lot like the "arbitrary precision" version of Option 2 except slightly worse on all counts. You're taking up more storage and the inserts are a lot less elegant. Also, what about when you want to initialise a list with more than 30 elements? Do you get to "z" then continue with "za", "zb"... ?

yorwba 6y ago

> the items new sort order value is the value of the item above it concatenated to an "a" Say you insert three items. a: 1, b: 2, c: 3. Then you reorder 3 after 1. The new order is a: 1, aa: 3, b:2. Then you reorder 2 after 1. The new order is a: 1, aa: 2, aa: 3. But since there are now two values with the same sort key, it might as well be a: 1, aa: 3, aa: 2. Did I misunderstand your solution or is that a bug?

deleted 6y ago
[deleted]
lukeramsden 6y ago
Here is a StackOverflow Q&A about something just like this: https://stackoverflow.com/questions/38923376/return-a-new-string-that-sorts-between-two-given-strings https://stackoverflow.com/questions/38923376/return-a-new-st... A similar system is used in Jira called LexoRank
ramraj07 6y ago
I ran into this problem and instead of solving it the proper way I took the lazy route by adding a unique constraint on the column and when it threw an errror in the insert I just added one more extra letter!
- n4r9 6y ago
  It's a neat idea, but this edge case makes it seem a lot like the "arbitrary precision" version of Option 2 except slightly worse on all counts. You're taking up more storage and the inserts are a lot less elegant. Also, what about when you want to initialise a list with more than 30 elements? Do you get to "z" then continue with "za", "zb"... ?