Assign an integer position to everything in the list. To insert something in the middle of the list, you assign it that position and then increment the position each item in the list after it by one. Can require a lot more writes than other approaches.
You can insert an item between two others with a single operation by taking the average of the positional values (indexes) of the two surrounding items and making that the position of the item being inserted.
- Decimal / Floating-Point - runs out of precision fairly quickly
- Fractional / True Fractions - much more precision, trickier to implement
- Large Int Boundaries - add first item at
0, add second item at average of0andMAX_INT. Need to insert at the front of the list, average lowest index item withMIN_INT. source
Similar to the (numeric) positional averaging approaches, but uses strings instead to get much more precision. Lexicographical ordering is used. To insert between two items, add a character to the end of the string that will position it between the two items.
Store an ordered array of element IDs either as a jsonb column or stringified
JSON in a text column on the collection table. If you have list_items
that are ordered, then the lists table will have an ordering column of
list_items IDs. You lose referential integrity with this approach.
Here is an example implementation.
A singly or doubly linked list where each item points to the next item in the list (and in the case of the doubly, points back to its previous item).
- User-defined Order in SQL
- Building Sortable Relations with PostgreSQL
- User Defined Ordering Made Easy
- Realtime Editing of Ordered Sequences (fractional indexing)
- Numeric and String Indexing
- Keeping an ordered collection in PostgreSQL
- Jira’s ranking system explained
- JIRA LexoRank Explained
- User-defined Order in SQL | Hacker News