From: Franco Bruno Borghesi <fborghesi@gmail.com>
To: andi <andi@mobile-elab.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
Date: Wed, 24 May 2006 10:04:20 -0300
Message-ID: <e13c14ec0605240604j4bd6a5abxa6cb846f1fdb0a18@mail.gmail.com> (raw)
In-Reply-To: <20060524103549.1E41C5AF071@svr4.postgresql.org>
References: <20060524103549.1E41C5AF071@svr4.postgresql.org>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fborghesi@gmail.com, andi@mobile-elab.com
Subject: Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
In-Reply-To: <e13c14ec0605240604j4bd6a5abxa6cb846f1fdb0a18@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox