How to perform an arithmetic operation by changing numerous database fields efficiently?

Question

There is a table with 200 records and one of the fields is responsible for indicating the order that these data are shown (0, 1, 2, 3, 4, 5, ...). I need to rearrange this data, take the record from position 167 and put it in position 3, and obviously what was in position 3 will go to position 4, and 4 to position 5, and so on. The problem is that I will have to change the "position" field of the 164 records that had the position modified. Is there any efficient way to do this? Or will I have to loop by changing each record individually? What if more than one record has to be rearranged?

Note: I am working with Python, Postgresql and Django

Answer 1

I made this function in Postgresql that you can use, just change to their respective fields:

CREATE OR REPLACE FUNCTION public.reordenar (antiga integer,nova integer)
RETURNS boolean AS
$body$
declare
aux integer;
begin
aux := (Select pk from registros where ordem = $1 limit 1);

update registros set 
ordem = 
    case when $1 > $2 then 
        ordem + 1 
    else 
        ordem - 1 
    end  
where 
    case when $1 > $2 then
        ordem >= $2 and ordem <= $1 
    else
        ordem >= $1 and ordem <= $2
    end;

update registros set ordem = $2 where pk = aux;


return true;

end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

I considered that the sorting field and the primary key are of the type integer and that there is no repetition in ordination but there is also a restriction as to duplicity.

ps. I believe that only the postgresql function solves your problem, the Phyton or Django part would be just to call the function.

See if it helps you.

Answer 2

The point here is to get the item n and decrease it from p. In this case, the elements of the range [n - p, n) need to be incremented. In SQL, these two update operations would be the following value update:

UPDATE tabela
SET
    id = CASE WHEN id = n THEN n - p
            ELSE id + 1
            END
WHERE
    id >= n - p AND id <= n

To call an UPDATE query directly from Django, I am using that documentation officer.

from django.db import connection

def my_custom_update(n, p):
    with connection.cursor() as cursor:
        cursor.execute("""UPDATE tabela
            SET
                id = CASE WHEN id = %s THEN %s
                        ELSE id + 1
                END
            WHERE
                id >= %s AND id <= %s
        """, [n, n - p, n - p, n])