Easy way to maintain an ordered list in SQL database

Rayen — Wed, 29 Jan 2025 08:16:20 +0000

This guide explains how to efficiently manage task ordering using a SQL table with an order_index column. The approach ensures unique ordering and allows seamless reordering of tasks.

Implementation

Table Definition

We create a temporary table called tasks with an order_index column to maintain task order:

CREATE TEMPORARY TABLE tasks(
  id integer primary key,
  name varchar not null,
  order_index DOUBLE PRECISION DEFAULT EXTRACT(epoch FROM CURRENT_TIMESTAMP)
);
ALTER TABLE tasks ADD CONSTRAINT "order_index_UQ" UNIQUE(order_index);

Explanation

The order_index column is assigned a default value based on the current timestamp, ensuring each task gets a unique order value at creation.
The UNIQUE constraint guarantees that no two tasks share the same order_index.

Inserting a new task

By default, new tasks receive the highest order index (latest timestamp). If you want to insert a task with the lowest order, you can multiply the timestamp by -1:

INSERT INTO tasks (name, order_index)
VALUES ('Task 1', -1 * EXTRACT(EPOCH FROM CURRENT_TIMESTAMP));

Why Multiply by -1?

This ensures the task appears at the beginning of the list.
Positive timestamps naturally sort later, while negative timestamps sort earlier.

Updating Task Order

To reorder a task, the client must provide:

taskAboveId: ID of the task above the target task
taskBelowId: ID of the task below the target task
taskId: ID of the task being moved

Using these, the new order_index is calculated as the average of the surrounding task indices:

const taskAbovePosition = taskAbove ? taskAbove.order_index : taskBelow.order_index + 1;
const taskBelowPosition = taskBelow ? taskBelow.order_index : taskAbove.order_index - 1;
const newPosition = (taskAbovePosition + taskBelowPosition) / 2;

Why Use Averaging?

Ensures the new task order remains between its neighbors without requiring a full reorder.
Helps avoid conflicts and reuses available space in the floating-point range.

DEV Community: Rayen

Easy way to maintain an ordered list in SQL database

Implementation

Table Definition

Inserting a new task

Updating Task Order