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

Postgresql – How to increment a column that is part of a constraint?

I’m storing a list for each user.

username|index|...
john    | 1   |a
john    | 2   |b
john    | 3   |c
jane    | 1   |...
jane    | 2   |...

Suppose I were to do an addition for john before his index 2, I have to increment the index so that the new table becomes:

username|index|...
john    | 1   |a
john    | 2   |new value
john    | 3   |b
john    | 4   |c
jane    | 1   |...
jane    | 2   |...

My solution is to first increment john’s index that is greater than or equals to 2; and then do an insert statement.

The problem is that this produces a duplicate error:

UPDATE mytable
SET index = index + 1
WHERE username = 'john' AND
    index >= 2

This is because I have a UNIQUE constraint on mytable (username, index).

What is the workaround for this?

Tags:

2

Answers


  1. 0

    You need to create a DEFERRABLE unique constraint for that to work, because such a constraint will be checked at the end of the statement.

    However, it is silly to update many rows when a new one is added. Use a double precision column to persist the order, because then you can always insert new values between old ones. Assign integer numbers to the order using row_number() when you query the table.

    Login or Signup to reply.
  2. 0

    Apart from the fact that a view would be the best option to handle such kind of index, one way to do it is to have a subquery whose order of indices is inverted, so that older indices are updated first. Relevant is the FOR UPDATE clause, that the subquery has to have at the bottom of it.

    UPDATE tab
SET idx = cte.idx + 1
FROM (SELECT idx 
      FROM tab
      WHERE idx >= 2 
      ORDER BY idx DESC 
      FOR UPDATE) cte
WHERE tab.username = 'john' 
  AND tab.idx = cte.idx;

SELECT * FROM tab

    Output:

    username idx
    john 1
    jane 1
    jane 2
    john 4
    john 3

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search