Thread: renumber id's in correct order (compact id's)
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@goldfisch.at
How about: update table set id = (select count(*) from table t2 where t2.id <= table.id); Ugly as hell, but it should work. 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@goldfisch.at > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
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@goldfisch.at
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org 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@goldfisch.at
On Tue, Jun 21, 2005 at 03:23:07PM +0200, peter pilsl wrote: > 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. Well, that's because you're typing the query wrong. Because you said: where t2.id <= voev_content.id It's going to order them by the id (which you didn't show in your query which is why it's not obvious). If you want to order by rank you should do (your query search-replace id for rank): knowledge=# update voev_content set rank = (select count(*) from voev_content t2 where t2.rank <= voev_content.rank and t2.kategorie=5 and t2.rank !=0) where kategorie=5 and rank!=0; Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
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@aqsacom.com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of peter pilsl
Sent: mardi 21 juin 2005 15:23
To: Martijn van Oosterhout
Cc: PostgreSQL List
Subject: Re: [GENERAL] 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@goldfisch.at
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org 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@goldfisch.at
---------------------------(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
Martijn van Oosterhout wrote: >> >>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. > > > Well, that's because you're typing the query wrong. Because you said: > > where t2.id <= voev_content.id > > It's going to order them by the id (which you didn't show in your query > which is why it's not obvious). If you want to order by rank you should > do (your query search-replace id for rank): > ;) thnx a lot. While I was reading the manuals to reveal the secrets of transaction-levels in update-operations I simply missed the obvious: a typo when moving the command from my test-table to the real-world-table. Thnx a lot for your help. Now everything is working perfekt. peter