If you don't mind creating a psql function, I guess you could do something like that CREATE OR REPLACE FUNCTION Update_voev_content( ) RETURNS int4 AS $$ DECLARE _record RECORD; _rank int4; BEGIN _rank := 0; FOR _record IN ( SELECT rank FROM voev_content ORDER BY rank ) LOOP UPDATE voev_content SET rank = _rank WHERE rank = _record.rank; _rank := _rank + 1; END LOOP; RETURN _rank; END $$ LANGUAGE 'plpgsql'; then SELECT Update_voev_content() should do the trick.... Regards, Patrick ---------------------------------------------------------------------------- --------------- Patrick Fiche email : patrick.fiche@xxxxxxxxxxx tél : 01 69 29 36 18 ---------------------------------------------------------------------------- --------------- -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx]On Behalf Of peter pilsl Sent: mardi 21 juin 2005 15:23 To: Martijn van Oosterhout Cc: PostgreSQL List Subject: Re: renumber id's in correct order (compact id's) Martijn van Oosterhout wrote: > How about: > > update table set id = (select count(*) from table t2 where t2.id <= table.id); > > Ugly as hell, but it should work. > thnx a lot. But it does not work as expected cause the update-statement ist not commiting for the whole table during the execution. So the resulting order can be different from the original order, which is what I try to avoid. example with real-work-database. entries with rank=0 are excluded from the query. knowledge=# select rank,kategorie,titel from voev_content where kategorie=5 order by rank; rank | kategorie | titel ------+-----------+---------------------- 0 | 5 | hauptaktivitäten 3 | 5 | test 4 | 5 | startseite 5 | 5 | Salzburger Gespräche (4 rows) knowledge=# update voev_content set rank = (select count(*) from voev_content t2 where t2.id <= voev_content.id and t2.kategorie=5 and t2.id !=0) where kategorie=5 and rank!=0; UPDATE 3 knowledge=# select rank,kategorie,titel from voev_content where kategorie=5 order by rank; rank | kategorie | titel ------+-----------+---------------------- 0 | 5 | hauptaktivitäten 1 | 5 | Salzburger Gespräche 2 | 5 | test 3 | 5 | startseite (4 rows) note that test now is ordered as second (excluding the rank=0-entry) while it was ordered first in the original configuration. thnx, peter > Hope this helps, > > On Tue, Jun 21, 2005 at 10:06:40AM +0200, peter pilsl wrote: > >> >>I've entries with id's like: >> >> x | id >>---+---- >> b | 1 >> a | 4 >> e | 5 >> c | 12 >> d | 19 >>(5 rows) >> >> >>now I'd like to have the id in continuing number to get: >> >> x | id >>---+---- >> b | 1 >> a | 2 >> e | 3 >> c | 4 >> d | 5 >>(5 rows) >> >> >>Simpliest way to do would be to create a sequence and update the whole >>table using nextval on the sequencec. Unfortunately UPDATE does not know >>about an order-statement. >> >>Any Idea, >>thnx, >>peter >> >> >> >> >> >>-- >>mag. peter pilsl >>goldfisch.at >>IT-management >>tel +43 699 1 3574035 >>fae +43 699 4 3574035 >>pilsl@xxxxxxxxxxxx >> >>---------------------------(end of broadcast)--------------------------- >>TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your >> message can get through to the mailing list cleanly > > -- mag. peter pilsl goldfisch.at IT-management tel +43 699 1 3574035 fae +43 699 4 3574035 pilsl@xxxxxxxxxxxx ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
