Question 174123 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to reorder a PostgresSQL table based on a column, when inserting, updating or deleting a row?

How to reorder a PostgreSQL table based on a column, when inserting, updating, or deleting a row?

Say I have a table questions, and I have a column called questionOrder (an integer), which sets the order of the questions.

If I want to add a question with questionOrder = 5, and there are already 10 questions in the table, how do I update the order of the existing questions to cater to the new question with questionOrder = 5, i.e. all the current questions with questionOrder = 5 and greater should be incremented by one?

In the same aspect, if I delete a question, how do I reorder the table also?

If there is a more correct approach to designing a table/method of doing so, please do point me in that direction, it would be appreciated.

Essentially, I need a way to reorder the data in a table based on a column whenever that column value is updated, inserted, or deleted.

Tags:

2

Answers


  1. 0

    You could use an update followed by an insert:

    UPDATE questions
SET questionOrder = questionOrder + 1
WHERE questionOrder >= 5;

INSERT INTO questions (questionOrder, question)
VALUES (5, 'your new question here');

    The update shifts questions currently numbered 5 or higher up by one. The insert then adds the new question at the 5 position.

    Login or Signup to reply.
  2. 0

    To be honest, I think this approach invites a lot of maintenance complications you can avoid by not actually storing the question order.

    Instead, you can store some value that represents the relative order of the questions, and then generate the "proper" order when you query it.
    This order can be saved as a double so you can always (up to the limitation of double‘s precision) insert a new question between two existing ones. If this precision isn’t enough, you could always go with a version-like string.

    Consider having questions 1 through 10:

    question_order question
    1 question 1
    2 question 2
    3 question 3
    4 question 4
    5 question 5
    6 question 6
    7 question 7
    8 question 8
    9 question 9
    10 question 10

    And now you’ve decided you have a new question that you want as question 5 and to "shift" all the other questions up. Also, you decided you no longer want question 9.
    With this approach, you don’t need to worry about rearranging the existing questions – you just insert the new question between the existing 4 and 5, and delete question 9:

    INSERT INTO questions(4.5, 'New question');
DELETE FROM questions WHERE question_order = 9;

    The actual data, after these DML statements would look like this:

    question_order question
    1 question 1
    2 question 2
    3 question 3
    4 question 4
    5 question 5
    6 question 6
    7 question 7
    8 question 8
    10 question 10
    4.5 New question

    But when you query it, you could create a nicer numerical order:

    SELECT   ROW_NUMBER() OVER (ORDER BY question_order ASC) AS num, question
FROM     questions
ORDER BY 1

    and get the following result:

    question_order question
    1 question 1
    2 question 2
    3 question 3
    4 question 4
    5 New question
    6 question 5
    7 question 6
    8 question 7
    9 question 8
    10 question 10
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search