pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
11+ messages / 7 participants
[nested] [flat]
* PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-24 10:35 andi <andi@mobile-elab.com>
0 siblings, 3 replies; 11+ messages in thread
From: andi @ 2006-05-24 10:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-24 11:27 Andrew Sullivan <ajs@crankycanuck.ca>
parent: andi <andi@mobile-elab.com>
2 siblings, 0 replies; 11+ messages in thread
From: Andrew Sullivan @ 2006-05-24 11:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-24 13:04 Franco Bruno Borghesi <fborghesi@gmail.com>
parent: andi <andi@mobile-elab.com>
2 siblings, 0 replies; 11+ messages in thread
From: Franco Bruno Borghesi @ 2006-05-24 13:04 UTC (permalink / raw)
To: andi <andi@mobile-elab.com>; +Cc: 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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-24 15:03 Richard Huxton <dev@archonet.com>
parent: andi <andi@mobile-elab.com>
2 siblings, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2006-05-24 15:03 UTC (permalink / raw)
To: andi <andi@mobile-elab.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-26 10:11 andi <andi@mobile-elab.com>
0 siblings, 1 reply; 11+ messages in thread
From: andi @ 2006-05-26 10:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-26 10:50 Andrew Sullivan <ajs@crankycanuck.ca>
parent: andi <andi@mobile-elab.com>
0 siblings, 2 replies; 11+ messages in thread
From: Andrew Sullivan @ 2006-05-26 10:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-26 14:08 Bruno Wolff III <bruno@wolff.to>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 1 reply; 11+ messages in thread
From: Bruno Wolff III @ 2006-05-26 14:08 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-26 14:22 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 11+ messages in thread
From: Andrew Sullivan @ 2006-05-26 14:22 UTC (permalink / raw)
To: pgsql-sql; +Cc: Bruno Wolff III <bruno@wolff.to>; Andrew Sullivan <ajs@crankycanuck.ca>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-27 09:28 Jesper K. Pedersen <jkp@solnet.homeip.net>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 11+ messages in thread
From: Jesper K. Pedersen @ 2006-05-27 09:28 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
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 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.
</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 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.
</pre>
</blockquote>
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.<br>
<br>
Best regards<br>
Jesper K. Pedersen<br>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-29 02:52 andi <andi@mobile-elab.com>
0 siblings, 1 reply; 11+ messages in thread
From: andi @ 2006-05-29 02:52 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
@ 2006-05-29 04:18 Alvaro Herrera <alvherre@commandprompt.com>
parent: andi <andi@mobile-elab.com>
0 siblings, 0 replies; 11+ messages in thread
From: Alvaro Herrera @ 2006-05-29 04:18 UTC (permalink / raw)
To: andi <andi@mobile-elab.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-05-29 04:18 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-24 10:35 PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly andi <andi@mobile-elab.com>
2006-05-24 11:27 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-05-24 13:04 ` Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly Franco Bruno Borghesi <fborghesi@gmail.com>
2006-05-24 15:03 ` Richard Huxton <dev@archonet.com>
2006-05-26 10:11 Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly andi <andi@mobile-elab.com>
2006-05-26 10:50 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-05-26 14:08 ` Bruno Wolff III <bruno@wolff.to>
2006-05-26 14:22 ` Andrew Sullivan <ajs@crankycanuck.ca>
2006-05-27 09:28 ` Jesper K. Pedersen <jkp@solnet.homeip.net>
2006-05-29 02:52 Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly andi <andi@mobile-elab.com>
2006-05-29 04:18 ` Alvaro Herrera <alvherre@commandprompt.com>
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