From andi@mobile-elab.com Wed May 24 10:56:55 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 033599FA5D7 for ; Wed, 24 May 2006 07:56:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 58408-01 for ; Wed, 24 May 2006 07:56:42 -0300 (ADT) X-Greylist: delayed 00:20:39.414589 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 53D5B9FA2FA for ; Wed, 24 May 2006 07:56:42 -0300 (ADT) Received: from mobile-elab.com (dsl-53.230-225.indo.net.id [202.53.230.225]) by svr4.postgresql.org (Postfix) with ESMTP id 1E41C5AF071 for ; Wed, 24 May 2006 10:35:49 +0000 (GMT) Received: from andi ([192.168.1.28]) by mobile-elab.com (mobile-elab.com) (MDaemon.PRO.v8.1.3.R) with ESMTP id md50000103055.msg for ; Wed, 24 May 2006 17:33:59 +0700 From: "andi" To: Subject: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Date: Wed, 24 May 2006 17:35:10 +0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000F_01C67F58.686D9720" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcZ/HbvCXDF6v1TMSduwAyt/ex1aQw== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-Spam-Processed: mobile-elab.com, Wed, 24 May 2006 17:33:59 +0700 (not processed: message from valid local sender) X-MDRemoteIP: 192.168.1.28 X-Return-Path: andi@mobile-elab.com X-MDaemon-Deliver-To: pgsql-sql@postgresql.org Message-Id: <20060524103549.1E41C5AF071@svr4.postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.25 tagged_above=0 required=5 tests=BAYES_50, FORGED_RCVD_HELO, HTML_90_100, HTML_MESSAGE, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200605/182 X-Sequence-Number: 25213 This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C67F58.686D9720 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Dear friends, I have table MD_CUSTOMER MD_CUSTOMERIDPK integer primary key NAME varchar But my primary key is not in correct order like MD_CUSTOMER MD_CUSTOMERIDPK NAME 10 ANDI 33 TESTER 100 KKK , so I want to make other primary key to generate sequences 1, 2, 3, . and in MS SQL SERVER 2005 I can with Rank() function , but in Postgres how ? PLEASE any one can help me, I am really appreciate. Best regards Andi kusnadi ------=_NextPart_000_000F_01C67F58.686D9720 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Dear friends,

I have table

MD_CUSTOMER

MD_CUSTOMERIDPK integer primary key

NAME &nb= sp; &nbs= p; = varchar

But my primary key is not in correct order = like

MD_CUSTOMER<= /b>

MD_CUSTOMERIDPK        &n= bsp;            = NAME

10           = ;            =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =             &= nbsp;   ANDI

33           = ;            =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;            =             &= nbsp;   TESTER

100       &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;    KKK

, so I want to make other primary key to generate sequences 1, = 2, 3, … and in MS SQL SERVER 2005

I can with Rank() function , but in Postgres how = ?

PLEASE any one can help me, I am really = appreciate.

Best regards

Andi kusnadi

------=_NextPart_000_000F_01C67F58.686D9720-- From ajs@crankycanuck.ca Wed May 24 11:27:50 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E61799FA2FA for ; Wed, 24 May 2006 08:27:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 61106-04 for ; Wed, 24 May 2006 08:27:36 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from r1.mycybernet.net (r1.mycybernet.net [209.5.206.8]) by postgresql.org (Postfix) with ESMTP id 5C1E19FA43B for ; Wed, 24 May 2006 08:27:23 -0300 (ADT) Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]:37501 helo=phlogiston.dydns.org) by r1.mycybernet.net with esmtp (Exim 4.50 (FreeBSD)) id 1FirWL-000CKA-43 for pgsql-sql@postgresql.org; Wed, 24 May 2006 07:27:21 -0400 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 6F7DD3FAF; Wed, 24 May 2006 07:27:17 -0400 (EDT) Date: Wed, 24 May 2006 07:27:17 -0400 From: Andrew Sullivan To: pgsql-sql@postgresql.org Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Message-ID: <20060524112717.GC31756@phlogiston.dyndns.org> References: <20060524103549.1E41C5AF071@svr4.postgresql.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060524103549.1E41C5AF071@svr4.postgresql.org> User-Agent: Mutt/1.5.9i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/183 X-Sequence-Number: 25214 On Wed, May 24, 2006 at 05:35:10PM +0700, andi wrote: > > But my primary key is not in correct order like What does this mean? Is the key being generated by a sequence (i.e. is the column DEFAULT nextval('some_sequence'))? If so, the primary key will be assigned in COMMIT order. Note that the sequence does not guarantee no gaps, however. A -- Andrew Sullivan | ajs@crankycanuck.ca The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun From fborghesi@gmail.com Wed May 24 13:04:30 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 5C0A79FA2FA for ; Wed, 24 May 2006 10:04:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 72742-08 for ; Wed, 24 May 2006 10:04:24 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id EB7BC9FA2DB for ; Wed, 24 May 2006 10:04:22 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id l36so72861nfa for ; Wed, 24 May 2006 06:04:21 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Su1THHn19MGVy1KseXU99bwCjB24WKBQoWwkt1tWDX/1C3uqkn9VuPNc11TtxykGWwa2FrbtOdIWHYla49IYQlLisYND/aByqHSNFhczkin/2fTW+AvzEyrTb+7cD3KoyXiQlAZBRaUExnHHmCghdxZ4/Hq+LRExXMKtAb7AEH0= Received: by 10.48.31.7 with SMTP id e7mr3093513nfe; Wed, 24 May 2006 06:04:20 -0700 (PDT) Received: by 10.49.54.8 with HTTP; Wed, 24 May 2006 06:04:20 -0700 (PDT) Message-ID: Date: Wed, 24 May 2006 10:04:20 -0300 From: "Franco Bruno Borghesi" To: andi Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Cc: pgsql-sql@postgresql.org In-Reply-To: <20060524103549.1E41C5AF071@svr4.postgresql.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5698_24287669.1148475860243" References: <20060524103549.1E41C5AF071@svr4.postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/184 X-Sequence-Number: 25215 ------=_Part_5698_24287669.1148475860243 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Well, you could add a serial column. I'll tell you how, but I haven't teste= d the code, so be sure to check it! And using BEGIN and COMMIT/ROLLBACK to delimit transactions would not be a bad idea at all ;-) To add a serial column, just write: --create new serial field ALTER TABLE md_customer ADD id SERIAL; If you check your table now, you will see that your new 'id' column contain= s correlative values. If this is what you wanted, you could update every tabe referencing md_customer, like this: --drop foreign key on remote table ALTER TABLE xxx DROP CONSTRAINT xxx_fk; --set old pk values to the value in the 'id' field just created UPDATE xxx SET fk_field=3Dmd_customer.id FROM md_customer CU WHERE CU.md_customeridpk=3Dxxx.fk_field; Check if everything is ok now. If it is, then recreate your foreign key, drop your old pk and rename the new one: --restore fk on remote table ALTER TABLE xxx ADD CONSTRAINT xxx_fk FOREIGN KEY (fk_field) REFERENCES md_customer(id) ON DELETE ... ON UPDATE ...; --drop old pk ALTER TABLE md_customer DROP md_customeridpk CASCADE; --rename id to md_customeridpk ALTER TABLE md_customer RENAME id TO md_customeridpk; --create pk ALTER TABLE md_customer ADD CONSTRAINT md_customer_pk PRIMARY KEY(md_customeridpk). That should be it. Hope it helps. On 5/24/06, andi wrote: > > Dear friends, > > > > I have table > > MD_CUSTOMER > > *MD_CUSTOMERIDPK* integer primary key > > *NAME *varchar > > > > > > But my primary key is not in correct order like > > *MD_CUSTOMER* > > MD_CUSTOMERIDPK NAME > > 10 > ANDI > > 33 > TESTER > > 100 KKK > > > > , so I want to make other primary key to generate sequences 1, 2, 3, =85 = and > in MS SQL SERVER 2005 > > I can with Rank() function , but in Postgres how ? > > > > PLEASE any one can help me, I am really appreciate. > > > > Best regards > > > > Andi kusnadi > ------=_Part_5698_24287669.1148475860243 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Well, you could add a serial column. I'll tell you how, but I haven't teste= d the code, so be sure to check it! And using BEGIN and COMMIT/ROLLBACK to = delimit transactions would not be a bad idea at all ;-)

To add a ser= ial column, just write:

--create new serial field
ALTER TABLE md_customer ADD id SERIAL;

If you check your table now, you will see that your new 'id' column contain= s correlative values. If this is what you wanted, you could update every ta= be referencing md_customer, like this:

--drop foreign key on remote = table
ALTER TABLE xxx DROP CONSTRAINT xxx_fk;

--set old pk values to t= he value in the 'id' field just created
UPDATE xxx SET fk_field=3Dmd_cus= tomer.id FROM md_customer CU WHERE CU.md_customeridpk=3Dxxx.fk_field;
Check if everything is ok now. If it is, then recreate your foreign key, dr= op your old pk and rename the new one:

--restore fk on remote table<= br>ALTER TABLE xxx ADD CONSTRAINT xxx_fk FOREIGN KEY (fk_field) REFER= ENCES md_customer(id) ON DELETE ... ON UPDATE ...;

--drop old pk
ALTER TABLE md_customer DROP md_customeridpk CASCA= DE;

--rename id to md_customeridpk
ALTER TABLE md_customer RENAME= id TO md_customeridpk;

--create pk
ALTER TABLE md_customer ADD C= ONSTRAINT md_customer_pk PRIMARY KEY(md_customeridpk).

That should be it.
Hope it helps.

On 5/24/06, andi <andi@mobile-elab.co= m> wrote:

= Dear friends,

I have table

MD_CUSTOMER

MD_CUSTOMERIDPK integer primary key

NAME      =             &nb= sp;        varchar

But my primary key is not in correct order like<= /p>
MD_CUSTOMER

MD_CUSTOMERIDPK         &nb= sp;           NAME=

10      &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;     ANDI

33      &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;     TESTER

100      &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;   KKK

, so I want to make other primary key to generate sequences 1, 2, 3, =85 and in MS SQL SERVER 2005

I can with Rank() function , but in Postgres how ?

PLEASE any one can help me, I am really appreciate.

Best regards

Andi kusnadi

------=_Part_5698_24287669.1148475860243-- From dev@archonet.com Wed May 24 15:03:44 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 536D49F9F8F for ; Wed, 24 May 2006 12:03:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 89294-07 for ; Wed, 24 May 2006 12:03:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pih-relay05.plus.net (pih-relay05.plus.net [212.159.14.132]) by postgresql.org (Postfix) with ESMTP id 0E9849FA2DB for ; Wed, 24 May 2006 12:03:33 -0300 (ADT) Received: from [213.162.97.75] (helo=mail.metronet.co.uk) by pih-relay05.plus.net with esmtp (Exim) id 1FiutX-0004IH-HC; Wed, 24 May 2006 16:03:31 +0100 Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 23DC873657; Wed, 24 May 2006 16:03:27 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id C77C115EA6; Wed, 24 May 2006 16:03:26 +0100 (BST) Message-ID: <447475BE.8050200@archonet.com> Date: Wed, 24 May 2006 16:03:26 +0100 From: Richard Huxton User-Agent: Thunderbird 1.5.0.2 (X11/20060420) MIME-Version: 1.0 To: andi Cc: pgsql-sql@postgresql.org Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly References: <20060524103549.1E41C5AF071@svr4.postgresql.org> In-Reply-To: <20060524103549.1E41C5AF071@svr4.postgresql.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/185 X-Sequence-Number: 25216 andi wrote: > Dear friends, > > I have table > > MD_CUSTOMER > > MD_CUSTOMERIDPK integer primary key > NAME varchar OK - two columns. > But my primary key is not in correct order like > > MD_CUSTOMER > > MD_CUSTOMERIDPK NAME > > 10 > ANDI > > 33 > TESTER > > 100 KKK Not sure what you mean. What does it mean for your primary key to be in the "correct order"? I assume you know how to select rows in a specific order using the "ORDER BY" clause? > , so I want to make other primary key to generate sequences 1, 2, 3, . and How can you have *another* primary key? By definition there can only be one primary key. > in MS SQL SERVER 2005 > > I can with Rank() function , but in Postgres how ? If all you want to do is generate a series of numbers you might look at generate_series(), or if you'd like a "row number" then something like: CREATE TEMPORARY SEQUENCE myseq; SELECT *,nextval('myseq') FROM mytable; I have to say though, I'm not sure what you're trying to do. I do get the feeling I'd think it was a bad idea once I found out though. -- Richard Huxton Archonet Ltd From andi@mobile-elab.com Fri May 26 10:12:18 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1486C9FA5E6 for ; Fri, 26 May 2006 07:12:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 77199-05 for ; Fri, 26 May 2006 07:12:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 24E9B9FA5E2 for ; Fri, 26 May 2006 07:12:12 -0300 (ADT) Received: from mobile-elab.com (dsl-53.230-225.indo.net.id [202.53.230.225]) by svr4.postgresql.org (Postfix) with ESMTP id 5C4B35B0B83 for ; Fri, 26 May 2006 10:12:07 +0000 (GMT) Received: from andi ([192.168.1.28]) by mobile-elab.com (mobile-elab.com) (MDaemon.PRO.v8.1.3.R) with ESMTP id md50000104372.msg for ; Fri, 26 May 2006 17:10:12 +0700 From: "andi" To: Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Date: Fri, 26 May 2006 17:11:26 +0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0025_01C680E7.6C54B810" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcaArL+62aZzv44jSz+lUtctU+jDZA== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-Spam-Processed: mobile-elab.com, Fri, 26 May 2006 17:10:12 +0700 (not processed: message from valid local sender) X-MDRemoteIP: 192.168.1.28 X-Return-Path: andi@mobile-elab.com X-MDaemon-Deliver-To: pgsql-sql@postgresql.org Message-Id: <20060526101207.5C4B35B0B83@svr4.postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.25 tagged_above=0 required=5 tests=BAYES_50, FORGED_RCVD_HELO, HTML_90_100, HTML_MESSAGE, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200605/188 X-Sequence-Number: 25219 This is a multi-part message in MIME format. ------=_NextPart_000_0025_01C680E7.6C54B810 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit When I use this syntax select * from TESTER; I got TESTERIDPK TESTER_NAME 10 TESSSS 90 NAMAAAA 100 UUUUU In ms sql server 2005 I use this 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. Thank you ------=_NextPart_000_0025_01C680E7.6C54B810 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

When I use this = syntax

select * from TESTER;

I got

TESTERIDPK = TESTER_NAME

10          = TESSSS

90    =      NAMAAAA

100         = UUUUU

In ms sql server 2005 I use this =

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           &nbs= p;   UUUUUUUU

How in postgres sql I get the same = result , please help me, because iam really frustating with this = duty.

Thank = you

------=_NextPart_000_0025_01C680E7.6C54B810-- From ajs@crankycanuck.ca Fri May 26 10:50:45 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 645829FA279 for ; Fri, 26 May 2006 07:50:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 81534-02 for ; Fri, 26 May 2006 07:50:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from r1.mycybernet.net (r1.mycybernet.net [209.5.206.8]) by postgresql.org (Postfix) with ESMTP id 2E4B09F9B01 for ; Fri, 26 May 2006 07:50:38 -0300 (ADT) Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]:37710 helo=phlogiston.dydns.org) by r1.mycybernet.net with esmtp (Exim 4.50 (FreeBSD)) id 1FjZtt-000KX0-FI for pgsql-sql@postgresql.org; Fri, 26 May 2006 06:50:37 -0400 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 0F9393FAF; Fri, 26 May 2006 06:50:37 -0400 (EDT) Date: Fri, 26 May 2006 06:50:37 -0400 From: Andrew Sullivan To: pgsql-sql@postgresql.org Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Message-ID: <20060526105037.GB5492@phlogiston.dyndns.org> References: <20060526101207.5C4B35B0B83@svr4.postgresql.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060526101207.5C4B35B0B83@svr4.postgresql.org> User-Agent: Mutt/1.5.9i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/190 X-Sequence-Number: 25221 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. 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. A -- Andrew Sullivan | ajs@crankycanuck.ca "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler From bruno@wolff.to Fri May 26 14:06:30 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0EA6B9FA279 for ; Fri, 26 May 2006 11:06:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 48584-04 for ; Fri, 26 May 2006 11:06:19 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id B94E89F9B01 for ; Fri, 26 May 2006 11:06:19 -0300 (ADT) Received: (qmail 20708 invoked by uid 500); 26 May 2006 14:08:21 -0000 Date: Fri, 26 May 2006 09:08:20 -0500 From: Bruno Wolff III To: Andrew Sullivan Cc: pgsql-sql@postgresql.org Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Message-ID: <20060526140820.GA17450@wolff.to> Mail-Followup-To: Bruno Wolff III , Andrew Sullivan , pgsql-sql@postgresql.org References: <20060526101207.5C4B35B0B83@svr4.postgresql.org> <20060526105037.GB5492@phlogiston.dyndns.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060526105037.GB5492@phlogiston.dyndns.org> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/193 X-Sequence-Number: 25224 On Fri, May 26, 2006 at 06:50:37 -0400, Andrew Sullivan 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. From ajs@crankycanuck.ca Fri May 26 14:23:02 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id CC7399FA279 for ; Fri, 26 May 2006 11:23:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 53164-04 for ; Fri, 26 May 2006 11:22:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from r1.mycybernet.net (r1.mycybernet.net [209.5.206.8]) by postgresql.org (Postfix) with ESMTP id 76D4D9F9B01 for ; Fri, 26 May 2006 11:22:57 -0300 (ADT) Received: from 227-54-222-209.mycybernet.net ([209.222.54.227]:38478 helo=phlogiston.dydns.org) by r1.mycybernet.net with esmtp (Exim 4.50 (FreeBSD)) id 1FjdDL-0005s1-D2; Fri, 26 May 2006 10:22:55 -0400 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id D2D0B417B; Fri, 26 May 2006 10:22:54 -0400 (EDT) Date: Fri, 26 May 2006 10:22:54 -0400 From: Andrew Sullivan To: pgsql-sql@postgresql.org Cc: Bruno Wolff III , Andrew Sullivan Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Message-ID: <20060526142254.GB5939@phlogiston.dyndns.org> References: <20060526101207.5C4B35B0B83@svr4.postgresql.org> <20060526105037.GB5492@phlogiston.dyndns.org> <20060526140820.GA17450@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060526140820.GA17450@wolff.to> User-Agent: Mutt/1.5.9i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/194 X-Sequence-Number: 25225 On Fri, May 26, 2006 at 09:08:20AM -0500, Bruno Wolff III wrote: > > Rollbacks will not reset sequence values. Use setval to do that. No, what I posted was the CREATE SEQUENCE after the BEGIN. ROLLBACK gets rid of the sequence. The next time you create the same sequence, therefore, it also starts at 1. I don't actually know what this ranking is useful for, to be honest, but people ask for it, and this is a stupid Postgres trick that can make it happen. A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner From jkp@solnet.homeip.net Sat May 27 09:46:54 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 93C4F9FA2FA for ; Sat, 27 May 2006 06:46:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 38163-03 for ; Sat, 27 May 2006 06:46:47 -0300 (ADT) X-Greylist: delayed 00:17:36.966786 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 33E9E9F9EEB for ; Sat, 27 May 2006 06:46:48 -0300 (ADT) Received: from rhea.solnet (0x50c60afe.vgnxx3.adsl-dhcp.tele.dk [80.198.10.254]) by svr4.postgresql.org (Postfix) with ESMTP id 2BE5E5B318E for ; Sat, 27 May 2006 09:29:09 +0000 (GMT) Received: from [10.1.1.8] (wormhole.solnet [10.1.1.254]) (authenticated bits=0) by rhea.solnet (8.13.6/8.13.6) with ESMTP id k4R9bUcR008228 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Sat, 27 May 2006 11:37:33 +0200 Message-ID: <44781BCC.7000607@solnet.homeip.net> Date: Sat, 27 May 2006 11:28:44 +0200 From: "Jesper K. Pedersen" User-Agent: Mozilla Thunderbird 1.0.7-1.1.fc4 (X11/20050929) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly References: <20060526101207.5C4B35B0B83@svr4.postgresql.org> <20060526105037.GB5492@phlogiston.dyndns.org> In-Reply-To: <20060526105037.GB5492@phlogiston.dyndns.org> Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/195 X-Sequence-Number: 25226 Andrew Sullivan 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.

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.

You can not rely on the "order by" to sort your date at fetch time, it will read the date adding the nextval(...) at read time and then sort it.

Best regards
Jesper K. Pedersen

From andi@mobile-elab.com Mon May 29 03:12:25 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 67FA59FA131 for ; Mon, 29 May 2006 00:12:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 20293-01 for ; Mon, 29 May 2006 00:12:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id F06419FA32D for ; Mon, 29 May 2006 00:12:12 -0300 (ADT) Received: from mobile-elab.com (dsl-53.230-225.indo.net.id [202.53.230.225]) by svr4.postgresql.org (Postfix) with ESMTP id 775BA5B550A for ; Mon, 29 May 2006 03:10:41 +0000 (GMT) Received: from andi ([192.168.1.28]) by mobile-elab.com (mobile-elab.com) (MDaemon.PRO.v8.1.3.R) with ESMTP id md50000105483.msg for ; Mon, 29 May 2006 09:51:32 +0700 From: "andi" To: Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Date: Mon, 29 May 2006 09:52:50 +0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01C68305.A5966590" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcaCyvj76owwfVwDSJuKXMibfRvjMw== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-Spam-Processed: mobile-elab.com, Mon, 29 May 2006 09:51:32 +0700 (not processed: message from valid local sender) X-MDRemoteIP: 192.168.1.28 X-Return-Path: andi@mobile-elab.com X-MDaemon-Deliver-To: pgsql-sql@postgresql.org Message-Id: <20060529031041.775BA5B550A@svr4.postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.194 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200605/197 X-Sequence-Number: 25228 This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C68305.A5966590 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Dear friends, I have seen this is very drawback of our beloved postgres databases, postgres do not support sql 2003 standards, I hope soon we can support this standards. Thank you ------=_NextPart_000_0006_01C68305.A5966590 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Dear friends,

I have seen this is very drawback of our beloved = postgres databases, postgres do not support sql 2003 standards, =

I hope soon we can support this = standards.

Thank you

------=_NextPart_000_0006_01C68305.A5966590-- From alvherre@commandprompt.com Mon May 29 04:18:14 2006 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0915D9FA380 for ; Mon, 29 May 2006 01:18:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 26564-06 for ; Mon, 29 May 2006 01:18:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from alvh.no-ip.org (201-221-200-231.bk11-dsl.surnet.cl [201.221.200.231]) by postgresql.org (Postfix) with ESMTP id D354C9FA2FA for ; Mon, 29 May 2006 01:18:07 -0300 (ADT) Received: by alvh.no-ip.org (Postfix, from userid 1000) id D80E5C37378; Mon, 29 May 2006 00:18:04 -0400 (CLT) Date: Mon, 29 May 2006 00:18:04 -0400 From: Alvaro Herrera To: andi Cc: pgsql-sql@postgresql.org Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Message-ID: <20060529041804.GA19739@surnet.cl> Mail-Followup-To: andi , pgsql-sql@postgresql.org References: <20060529031041.775BA5B550A@svr4.postgresql.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060529031041.775BA5B550A@svr4.postgresql.org> User-Agent: Mutt/1.5.11+cvs20060403 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/198 X-Sequence-Number: 25229 andi wrote: > I have seen this is very drawback of our beloved postgres databases, > postgres do not support sql 2003 standards, > > I hope soon we can support this standards. Hmm. True. It is also true that we'll be there sooner if you help out. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support