CODEWITHSUNDEEP
sqlpostgresql
Daniel Gerber
Daniel Gerber

Reputation: 3470

PostgreSQL Query Optimization

I'm trying to generate new IDs for a large table. The ID's have to be consecutive and need to start at 0 (So I can't use sequence). What I come up with so far is the following function:

CREATE OR REPLACE FUNCTION genIds() RETURNS integer AS $$
DECLARE
   edge RECORD;
   i INTEGER := 0;
BEGIN
    FOR edge IN SELECT * FROM network LOOP
        UPDATE network SET id = i WHERE id = edge.id;
        i := i + 1;
    END LOOP;
    RETURN i;
END;
$$ LANGUAGE plpgsql;

I would much rather like to not care about id = edge.id since I don't really care about the id's anyway. Is there a way to avoid having count(network) updates?

Cheers, Daniel

Upvotes: 0

Views: 28

Answers (1)

user330315
user330315

Reputation:

Is there a way to avoid having count(network) updates?

If your question is: can this done with a single statement instead of a loop, then yes this is possible:

This can be done without a loop in a single statement:

with numbered as (
  select id as old_id, 
         row_number() over (order by id) as new_id
  from network
)
update network nt
  set id = nb.new_id - 1 // -1 to start at 0
from numbered nb
where nb.old_id = nt.id;

Upvotes: 1

Related Questions