Обсуждение: How do I create unique IDs for an existing set of records
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/
"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
> 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