X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0DA609DCA60 for ; Tue, 14 Mar 2006 21:11:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59384-03 for ; Tue, 14 Mar 2006 21:11:41 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from a2s5.a2hosting.com (host-209-124-50-10.a2webhosting.com [209.124.50.10]) by postgresql.org (Postfix) with ESMTP id 4D7719DC9A8 for ; Tue, 14 Mar 2006 21:11:36 -0400 (AST) Received: from adsl-6-32-197.tys.bellsouth.net ([65.6.32.197] helo=[192.168.1.10]) by a2s5.a2hosting.com with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.52) id 1FJKY5-0006nH-UH; Tue, 14 Mar 2006 20:11:38 -0500 In-Reply-To: <200603140919.49246.aarni@kymi.com> References: <200603140919.49246.aarni@kymi.com> Mime-Version: 1.0 (Apple Message framework v746.2) Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed Message-Id: Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable From: John DeSoi Subject: Re: Copying a row within table Date: Tue, 14 Mar 2006 20:11:32 -0500 To: aarni@kymi.com X-Mailer: Apple Mail (2.746.2) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - a2s5.a2hosting.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - pgedit.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.112 required=5 tests=[AWL=0.112] X-Spam-Score: 0.112 X-Spam-Level: X-Archive-Number: 200603/225 X-Sequence-Number: 24571 On Mar 14, 2006, at 2:19 AM, Aarni Ruuhim=E4ki wrote: > testing=3D# INSERT INTO foo (foo_1, foo_2, foo_3 ...) (SELECT foo_1, =20= > foo_2, > foo_3 ... FROM message_table WHERE foo_id =3D 10); > INSERT 717286 1 > testing=3D# > > Is there a fast way to copy all but not the PK column to a new row =20 > within the > same table so that the new foo_id gets its value from the sequence ? Here is an example using a plpgsql function: create or replace function test_duplicate (p_id integer) returns integer as $$ declare tt test%rowtype; begin select into tt * from test where id =3D p_id; tt.id :=3D nextval(pg_get_serial_sequence('test', 'id')); insert into test values (tt.*); return tt.id; end; $$ language plpgsql; John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL