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