Обсуждение: 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

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:

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

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 /> 

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