PostgreSQL Query Optimization

Question

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

Btw: you can make a sequence start at 0: create sequence foo_seq start with 0 minvalue 0; — user330315
– user330315, Commented Mar 31, 2016 at 9:36
They are not that's correct (I thought you just didn't want to use them because you couldn't make them start at 0). — user330315
– user330315, Commented Mar 31, 2016 at 11:55

Daniel Gerber · Accepted Answer · 2016-03-31 09:29:49Z

1

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;

edited Mar 31, 2016 at 9:29

Daniel Gerber

3,4803 gold badges27 silver badges32 bronze badges

answered Mar 31, 2016 at 9:05

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Query Optimization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related