Received: from falcon.prod.itd.earthlink.net (falcon.mail.pas.earthlink.net [207.217.120.74]) by postgresql.org (Postfix) with ESMTP id 129A347591D for ; Fri, 26 Apr 2002 11:59:36 -0400 (EDT) Received: from 1cust208.tnt2.richmond.in.da.uu.net ([63.26.248.208] helo=nick) by falcon.prod.itd.earthlink.net with smtp (Exim 3.33 #2) id 17188K-0000SK-00 for pgsql-sql@postgresql.org; Fri, 26 Apr 2002 08:59:41 -0700 Reply-To: From: "Nick Fankhauser" To: "PGSQL-SQL" Subject: How do I create unique IDs for an existing set of records Date: Fri, 26 Apr 2002 10:58:16 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 Importance: Normal X-Archive-Number: 200204/324 X-Sequence-Number: 7588 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/