Thread: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Dear friends,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I have table</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">MD_CUSTOMER</span></font><p class="MsoNormal"><b><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;font-weight:bold">MD_CUSTOMERIDPK</span></font></b><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">integer primary key</span></font><p class="MsoNormal"><b><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial;font-weight:bold">NAME </span></font></b><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">varchar</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">But my primary key is not in correct order like</span></font><p class="MsoNormal"><b><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial;font-weight:bold">MD_CUSTOMER</span></font></b><p class="MsoNormal"><font face="Times New Roman" size="3"><spanstyle="font-size: 12.0pt">MD_CUSTOMERIDPK NAME</span></font><p class="MsoNormal" style="margin-left:2.75in;text-indent:-2.5in;mso-list:l5level1 lfo5"><font face="Times New Roman" size="3"><span style="font-size:12.0pt"><spanstyle="mso-list:Ignore">10<font face="Times New Roman" size="1"><span style="font:7.0pt "TimesNew Roman""> </span></font></span></span></font>ANDI<pclass="MsoNormal" style="margin-left:2.75in;text-indent:-2.5in;mso-list:l4 level1lfo6"><font face="Times New Roman" size="3"><span style="font-size:12.0pt"><span style="mso-list:Ignore">33<font face="TimesNew Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></span></font>TESTER<pclass="MsoNormal" style="margin-left:.25in"><font face="Times New Roman" size="3"><spanstyle="font-size:12.0pt">100 KKK</span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">, so I want to make other primary key to generate sequences 1, 2, 3, … and in MS SQL SERVER 2005</span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">I can with Rank() function , but in Postgres how ?</span></font><p class="MsoNormal"><font face="Times New Roman"size="3"><span style="font-size: 12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">PLEASE any one can help me, I am really appreciate.</span></font><p class="MsoNormal"><font face="Times New Roman"size="3"><span style="font-size: 12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">Best regards</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">Andi kusnadi</span></font></div>
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
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:
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
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
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">When I use this syntax</span></font><p class="MsoNormal"><b><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue;font-weight:bold">select</span></font></b><b><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew"; font-weight:bold"> <font color="gray"><span style="color:gray">*</span></font> <font color="blue"><span style="color:blue">from</span></font>TESTER<font color="gray"><span style="color:gray">;</span></font></span></font></b><pclass="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New"">I got </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New"">TESTERIDPK TESTER_NAME</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New"">10 TESSSS</span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New"">90 NAMAAAA</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New"">100 UUUUU</span></font><p class="MsoNormal"><font color="gray" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:gray"> </span></font><p class="MsoNormal"><font face="CourierNew" size="2"><span style="font-size:10.0pt; font-family:"Courier New"">In ms sql server 2005 I use this </span></font><p class="MsoNormal"><b><font color="blue" face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New";color:blue;font-weight:bold">select</span></font></b><b><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew"; font-weight:bold"> rank<font color="gray"><span style="color:gray">()</span></font> <font color="blue"><span style="color:blue">over</span></font><fontcolor="gray"><span style="color:gray">(</span></font><font color="blue"><span style="color:blue">order</span></font><font color="blue"><span style="color:blue">by</span></font> testeridpk <font color="gray"><spanstyle="color:gray">)</span></font> <font color="blue"><span style="color:blue">as</span></font> rank <fontcolor="gray"><span style="color:gray">,</span></font> <font color="gray"><span style="color:gray">*</span></font> <fontcolor="blue"><span style="color:blue">from</span></font> tester<font color="gray"><span style="color:gray">;</span></font></span></font></b><pclass="MsoNormal"><b><font face="Courier New" size="2"><span style="font-size: 10.0pt;font-family:"Courier New";font-weight:bold">I get the result is like this, </span></font></b><p class="MsoNormal"><b><fontface="Courier New" size="2"><span style="font-size: 10.0pt;font-family:"Courier New";font-weight:bold"> </span></font></b><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New"">RANK TESTERIDPK TESTER_NAME</span></font><p class="MsoNormal"><font face="Courier New"size="2"><span style="font-size:10.0pt; font-family:"Courier New"">1 10 TESSS</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New"">2 90 NAMAAA</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New"">3 100 UUUUUUUU</span></font><p class="MsoNormal"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt; font-family:"Courier New""> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt; font-family:"Courier New""> </span></font><p class="MsoNormal"><b><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;font-weight:bold">How in postgres sql I get the same result , please help me, because iam really frustatingwith this duty.</span></font></b><p class="MsoNormal"><b><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;font-weight:bold"> </span></font></b><p class="MsoNormal"><b><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial;font-weight:bold">Thank you</span></font></b></div>
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 testersORDER 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
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.
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
Andrew Sullivan wrote: <blockquote cite="mid20060526105037.GB5492@phlogiston.dyndns.org" type="cite"><pre wrap="">On
Fri,May 26, 2006 at 05:11:26PM +0700, andi wrote: </pre><blockquote type="cite"><pre wrap="">select rank() over(order
bytesteridpk ) 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. </pre></blockquote><pre wrap="">
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 testersORDER 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. </pre></blockquote> You can not rely on the "order by"
tosort your date at fetch time, it will read the date adding the nextval(...) at read time and then sort it.<br /><br
/>Best regards<br /> Jesper K. Pedersen<br /><br />
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Dear friends,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I have seen this is very drawback of our beloved postgres databases, postgres do not support sql 2003standards, </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> I hope soon we can support this standards.</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Thank you</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font></div>
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