PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
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
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
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
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
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
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
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
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
On Fri, May 26, 2006 at 06:50:37 -0400, Andrew Sullivan 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.
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
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
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
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
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
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
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
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
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
Andrew Sullivan wrote: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.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.
Best regards
Jesper K. Pedersen
Re: PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
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
