0

I've got the following table

enter image description here

And I have to change the order of the rows in an unpredictable way for example:

  1. Set the position of the row with id = 1 after the row with id = 3 and preserve the ordering of the other rows.
  2. Add new row at position 3 right after row with id = 2.
  3. Delete row from position 4

What is the best solution for this problem?

Improve this question
4
  • With "reorder" rows, do you mean to update the "Order" column of your table to "move" the rows? – a_horse_with_no_name Feb 17 '20 at 9:13
  • @a_horse_with_no_name yes, I can insert row in some position and update the other rows but I think if table will grow in size it will affect the performance so I'm searching the better way – Шариф Латипов Feb 17 '20 at 9:16
  • See this answer for a possible solution: stackoverflow.com/questions/60115828 but there is no way to avoid updating all "following" rows if you want to move one row – a_horse_with_no_name Feb 17 '20 at 9:17
  • @a_horse_with_no_name what do you think about using arrays for this purpose? – Шариф Латипов Feb 17 '20 at 9:35
2

There is no solution, because table rows in PostgreSQL don't have a fixed position in the table. Every UPDATE will change the position, and every new row will be inserted wherever there happens to be enough room.

Even if there are no modifications, sequential scans of the table don't necessarily start at the beginning.

The way to order rows is to use an appropriate ORDER BY clause with SELECT.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.