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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

There is a table with 200 records and one of the fields is responsible for indicating the order that these data are displayed (0, 1, 2, 3, 4, 5, ...). I need to rearrange this data, pick up 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 5, and so on. The problem is that I will have to change the "position" field of the 164 records that had the modified position. Is there any efficient way to do this? Or do I have to loop through each record individually? What if you have to reorganize more than one record?

Note: I'm working with Python, PostgreSQL and Django

python postgresql django

asked by anonymous 25.08.2017 / 02:49

2 answers

RoR - Get the strong controller parameters inside the Application Controller Open a certain tab by clicking the navbar link

score 1 · 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 assumed that the sort field and the primary key are of type integer and that there is no repetition in sorting but there is also a constraint on duplicity.

ps. I think only the function in postgreSQL will solve your problem, the part of phyton or django would just be to call the function.

See if it helps.

score 0 · Answer 2

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

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'm using this official documentation .

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])