CODEWITHSUNDEEP
node.jspostgresqlexpress
codemon
codemon

Reputation: 1594

How to maintain list order in Postgres?

I am making my first postgres app with react. It's a simple todo list.

Right now when you retrieve the list of tasks you get the following:

pernstack=# SELECT * FROM todo;
 todo_id |          description
---------+-------------------------------
     825 | First task
     826 | Second Task
     824 | Third task
     822 | Fourth task
(4 rows)

The problem is if I edit the first task with a PUT request. That task gets put to the bottom of the list. Here is the edit request:

app.put("/todos/:id", async (req, res) => {
  try {
    const { id } = req.params;
    const { description } = req.body;
    const updateTodo = await pool.query(
      "UPDATE todo SET description = $1 WHERE todo_id = $2",
      [description, id]
    );

    res.json("Todo was updated");
  } catch (err) {
    console.error(err.message);
  }
});

What is the most basic way to edit the task without moving it out of order? I didn't want to havet to manually add an index column. In MySQL I believe this is done automatically.

Upvotes: 1

Views: 842

Answers (2)

Joe Lissner
Joe Lissner

Reputation: 2462

SELECT * FROM todo ORDER BY todo_id will make them always be in the order they were added, assuming todo_id is an auto-incremented number

If you want to be able to specify the order of the list in the future, you would want to add a column labeled list_order or something like that, then do SELECT * FROM todo ORDER BY list_order

Upvotes: 3

Mureinik
Mureinik

Reputation: 310993

Database tables are unordered sets. Unless you explicitly use an order by clause when querying the table (e.g., with a manual task_index column, like you suggested), you can not assume anything about the order returned from a query.

Upvotes: 2

Related Questions