pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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>
  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 16:14  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 16:17  Nick Fankhauser <nickf@ontko.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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