Decrement all values in a column after insert at top SQL

Question

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.

Curious if there is a more elegant answer to this than a trigger after insert updating priority = priority + 1 where priority +> inserted priority. — Twelfth
– Twelfth, Commented Mar 13, 2018 at 20:57
Seems like there is an ON CONFLICT model constraint that can update the row that has that unique value you are trying to insert. I wonder if this can be triggered successively? Insert at priority 3, priority 3 exists, update existing priority 3 to priority 4, priority 4 exists, update existing priorty 4 to priority 5, priority 5 exists... — Mark Pytel
– Mark Pytel, Commented Mar 13, 2018 at 21:24
Not sure how well that would cascade if you have more than a couple ID's being changed on insert. I'll give an answer that calculates priority on the fly — Twelfth
– Twelfth, Commented Mar 13, 2018 at 21:38
specs call for only one priority to be updated/inserted at a time. seems like cascading triggers are designed for this sort of behavior, database-programmer.blogspot.com/2008/05/… , now just to figure out if sequelize supports that — Mark Pytel
– Mark Pytel, Commented Mar 13, 2018 at 21:44
I meant if you had a database with priority 1 through 10001 and tried to insert priority 2...would the on conflict model constrain efficiently bump all 10k? I'd suspect that solution would not scale very well and likely result in a giant On Conflict loop — Twelfth
– Twelfth, Commented Mar 13, 2018 at 21:46

Twelfth · Accepted Answer · 2018-03-13 21:48:23Z

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

Collectives™ on Stack Overflow

Decrement all values in a column after insert at top SQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related