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

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFCt9/MIB7bNG8LQkwRAtXSAKCKTJHT+DoNqPcSVxk5181ALP6z1gCfR v/B
MTHNa6TgqcJqn5oAAmT5igQ=
=c9oT
-----END PGP SIGNATURE-----

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

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFCuBiBIB7bNG8LQkwRAvmEAJ4uKw4QM4uTObcG+N74l4 MID97z3ACgjLq6
HIglQc0yV6W+bgy5Q744p60=
=V3BF
-----END PGP SIGNATURE-----

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

---------------------------(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