Changing a column value to several records in a consecutive fashion using clever statements in SQL

I have a Postgres table that has an order view field in her, I’m using Nodejs with express

This order view is of type INTEGER and has the constraints of NOT NULL and UNIQUE

Inserting a new record may bring problems with that, because, if the existing records have view orders of, say, [1, 2, 3, 4, 5], and the new record has an order view of 4, then the current 4 must be changed to 5, and the current 5 must be changed to 6

(I’m forcing this order views to be consecutive numbers, from the front-end side)

I want this inserting process to be carried out at the server-side (updating the order views of the existing records)

What comes to my mind is to perform a cycle in the end-point, by reading the records that have an order view greater or equal than the new one, and updating it to (current + 1)

Another problem is with the related records in another table, but I think that it can be carried out with a cascading update

Can somebody figure out a SQL statement that does it without having to program that cycle?

Rafael

Answer

You can accomplish both the Insert and Update in a single statement. But first you need (most probably) change the unique constraint to make it deferrable.

alter table <table name>  
  drop constraint if exists <order view constraint name>;
alter <table name> 
  add constraint <order view constraint name> 
        unique(order_view) 
        deferrable initially deferred;

Postgres permits DML in a cte, so now you can write a SQL statement to accomplish both actions:

with updt as
     ( update <table name>  
          set order_view = order_view+1 
        where order_view >= :new_order_view
     ) 
insert into test (order_view, <other columns list>) 
   values (:new_order_view, <other columns list values>);

Where <…> is the appropriate names/values. See demo. The above can also be used as an SQL procedure – also in demo handling inserting as last item automatically setting appropriate value. While a procedure it is still just single SQL statement, so it can be pulled out and run standalone.

Shortcoming:

• Order_view specified as 0. Result will reorder resulting in skipping Order_view = 1.
• Order_view specified > current Max+1. Result stored as specified thus creating a gap.
• Subject to race conditions with multiple users. As are all assignments based on Max+1.