Decrement all values in a column after insert at top SQL

Before Inserting
Id Priority
1 . 1
2 . 2
3 . 3

After Inserting Id: 4, Priority 2
Id Priority
1 . 1
4 . 2
2 . 3
3 . 4

fairly new to postgres, and i have a table with a column named priority. this column should have unique values, and if you attempt to give a row a priority that already exists, it would basically insert it with that priority, and decrement all the priorities that are <= by one to accommodate it.

is there a term for this sort of behavior? i know it will involve a column with unique values, but are there any model constraints i can introduce to enable this sort of behavior? or do i need to manually code an algorithm to do this and account for all edge cases.

1 answer

  • answered 2018-03-13 20:51 Twelfth

    I wouldn't store priority as it's own field. Create the table as ID, priority, Date_entered. Then use:

    Select ID, rank() over (order by priority, date_entered) as priority

    I suspect since the rank can change so frequently, calculating it on the fly like this would be preferential to attempting to store the rank and keep it updated.

    edit: There is a logical flaw to this that I can spot already...if record 4 was inserted as priority 2 (so the database contains 2 priority 2 records), there really wouldn't be an easy way to inject ID 5 between ID 4 and 2 without manipulating the date_entered field.

    second edit: Allowing the priority column to be decimal (priority 2 entered, then priority 2.5 entered, and so on), then using the rank() function to resolve that to an integer would get around that. There isn't a pretty answer here that I can find