From: Nick Fankhauser <nickf@ontko.com>
To: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: How do I create unique IDs for an existing set of records
Date: Fri, 26 Apr 2002 10:58:16 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGOELNENAA.nickf@ontko.com> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nickf@ontko.com
Subject: Re: How do I create unique IDs for an existing set of records
In-Reply-To: <NEBBLAAHGLEEPCGOBHDGOELNENAA.nickf@ontko.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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