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@crankycanuck.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.