X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AF90DD1B20D for ; Tue, 20 Jul 2004 18:24:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37042-07 for ; Tue, 20 Jul 2004 21:24:44 +0000 (GMT) Received: from ms-smtp-03-eri0.ohiordc.rr.com (ms-smtp-03-smtplb.ohiordc.rr.com [65.24.5.137]) by svr1.postgresql.org (Postfix) with ESMTP id 716BED1B209 for ; Tue, 20 Jul 2004 18:24:41 -0300 (ADT) Received: from enzo.mascari.com (dhcp065-024-156-214.columbus.rr.com [65.24.156.214]) by ms-smtp-03-eri0.ohiordc.rr.com (8.12.10/8.12.7) with ESMTP id i6KLOXVd019673; Tue, 20 Jul 2004 17:24:33 -0400 (EDT) Received: from mascari.com (ferrari.mascari.com [192.168.2.1]) by enzo.mascari.com (8.12.8/8.12.8) with ESMTP id i6KLOVb8008567; Tue, 20 Jul 2004 17:24:31 -0400 Message-ID: <40FD8D8C.6000306@mascari.com> Date: Tue, 20 Jul 2004 17:24:28 -0400 From: Mike Mascari User-Agent: Mozilla/5.0 (Windows; U; WinNT4.0; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joost Kraaijeveld Cc: pgsql-general@postgresql.org Subject: Re: SQL (?) question References: In-Reply-To: X-Enigmail-Version: 0.82.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-mascari.com-MailScanner-Information: Please contact the ISP for more information X-mascari.com-MailScanner: Found to be clean X-mascari.com-MailScanner-SpamCheck: not spam, SpamAssassin (score=-4.9, required 6, autolearn=not spam, BAYES_00 -4.90) X-MailScanner-From: mascarm@mascari.com X-Virus-Scanned: Symantec AntiVirus Scan Engine X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/622 X-Sequence-Number: 63221 Joost Kraaijeveld wrote: > Hi all, > > Is there an easy way (e.g. short SQL script) to add a column to a > table and fill it with integers (from 1- n) just like using an > sequence? (I need to retofit a system id that is handled in the > application, not in the database). I assume there is some unique candidate key on the table already (or worst case, an OID.) If so, I'd just do: CREATE SEQUENCE my_sequence; CREATE TEMPORARY TABLE foo AS SELECT candidate_key, nextval('my_sequence') as new_key FROM table_that_needs_fixed; ALTER TABLE table_that_needs_fixed ADD COLUMN new_key integer; UPDATE table_that_needs_fixed SET new_key = foo.new_key WHERE candidate_key = foo.candidate_key; Add appropriate constraints as necessary... HTH, Mike Mascari