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

From Franco Bruno Borghesi
Subject Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
Date
Msg-id e13c14ec0605240604j4bd6a5abxa6cb846f1fdb0a18@mail.gmail.com
Whole thread Raw
In response to PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly  ("andi" <andi@mobile-elab.com>)
List pgsql-sql
Well, you could add a serial column. I'll tell you how, but I haven't tested 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 contains 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=md_customer.id FROM md_customer CU WHERE CU.md_customeridpk=xxx.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 <andi@mobile-elab.com> 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, … 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


pgsql-sql by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
Next
From: Richard Huxton
Date:
Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly