pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do I create unique IDs for an existing set of records
3+ messages / 2 participants
[nested] [flat]
* How do I create unique IDs for an existing set of records
@ 2002-04-26 15:58 Nick Fankhauser <nickf@ontko.com>
2002-04-26 16:14 ` Re: How do I create unique IDs for an existing set of records Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Nick Fankhauser @ 2002-04-26 15:58 UTC (permalink / raw)
To: pgsql-sql
Hi-
There's got to be a simple way to do this, but it's eluding me so far...
I have a table that is already populated with about 600,000 rows.
I have created a new unique id field for it. (alter table test add new_pk
int8 not null;)
I have also createtd a sequence (create sequence test_new_pk)
Now I want to populate the key for all of the existing records.
I tried:
update test set new_pk = (select nextval('test_new_pk_seq'));
but it wants to update *all* of the rows to the current nextval (I was
hoping it would evaluate nextval on each row).
How do I write an update to assign a sequential id for the existing records?
Any ideas appreciated!
-Nick
--------------------------------------------------------------------------
Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How do I create unique IDs for an existing set of records
2002-04-26 15:58 How do I create unique IDs for an existing set of records Nick Fankhauser <nickf@ontko.com>
@ 2002-04-26 16:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-26 16:17 ` Re: How do I create unique IDs for an existing set of records Nick Fankhauser <nickf@ontko.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2002-04-26 16:14 UTC (permalink / raw)
To: nickf@ontko.com; +Cc: pgsql-sql
"Nick Fankhauser" <nickf@ontko.com> writes:
> I tried:
> update test set new_pk = (select nextval('test_new_pk_seq'));
> but it wants to update *all* of the rows to the current nextval (I was
> hoping it would evaluate nextval on each row).
Yeah, the sub-select is taken (perhaps mistakenly) as something that
can be evaluated only once, because it doesn't depend on the outer
query. You are overthinking the problem; this should work:
update test set new_pk = nextval('test_new_pk_seq');
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How do I create unique IDs for an existing set of records
2002-04-26 15:58 How do I create unique IDs for an existing set of records Nick Fankhauser <nickf@ontko.com>
2002-04-26 16:14 ` Re: How do I create unique IDs for an existing set of records Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-04-26 16:17 ` Nick Fankhauser <nickf@ontko.com>
0 siblings, 0 replies; 3+ messages in thread
From: Nick Fankhauser @ 2002-04-26 16:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> You are overthinking the problem; this should work:
>
> update test set new_pk = nextval('test_new_pk_seq');
Thanks Tom! It did indeed work.
-Nick
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-04-26 16:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-26 15:58 How do I create unique IDs for an existing set of records Nick Fankhauser <nickf@ontko.com>
2002-04-26 16:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-26 16:17 ` Nick Fankhauser <nickf@ontko.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