Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly

On Fri, May 26, 2006 at 06:50:37 -0400,
Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
> On Fri, May 26, 2006 at 05:11:26PM +0700, andi wrote:
> > select rank() over(order by testeridpk ) as rank , * from tester;
> >
> > I get the result is like this,
> >
> >
> > RANK TESTERIDPK TESTER_NAME
> >
> > 1 10 TESSS
> >
> > 2 90 NAMAAA
> >
> > 3 100 UUUUUUUU
> >
> >
> > How in postgres sql I get the same result , please help me, because iam
> > really frustating with this duty.

The simplest solution is to add the rank information in your application as
it reads the result set.

> There's no built in for that that I know of. You could use a
> temporary sequence to do it:
>
> BEGIN;
> CREATE SEQUENCE tempseq;
> SELECT nextval('tempseq') as rank, testeridpk, tester_name FROM testers
> ORDER BY testeridpk;
> ROLLBACK;
>
> which, I _think_, will get you what you want (i.e. that's not
> tested). The ROLLBACK is just there to clean up the sequence.

Rollbacks will not reset sequence values. Use setval to do that.