Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly - Mailing list pgsql-sql

From Bruno Wolff III
Subject Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
Date
Msg-id 20060526140820.GA17450@wolff.to
Whole thread Raw
In response to Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly  (Andrew Sullivan <ajs@crankycanuck.ca>)
Responses Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
List pgsql-sql
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.


pgsql-sql by date:

Previous
From: Richard Huxton
Date:
Subject: Re: hi how to use encryption for incomtax in postgresql
Next
From: Andrew Sullivan
Date:
Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly