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 EA6079DCB72
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Mar 2006 13:41:16 -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 77916-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Mar 2006 13:41:15 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from ll.mit.edu (LLMAIL.LL.MIT.EDU [129.55.12.40])
	by postgresql.org (Postfix) with ESMTP id 80AB69DC883
	for <pgsql-sql@postgresql.org>; Tue, 14 Mar 2006 13:41:13 -0400 (AST)
Received: (from smtp@localhost)
	by ll.mit.edu (8.12.10/8.8.8) id k2EHfALl004486;
	Tue, 14 Mar 2006 12:41:10 -0500 (EST)
Received: from UNKNOWN(            ), claiming to be "sty.llan"
	via SMTP by llmail, id smtpdAAA6OaO4h; Tue Mar 14 12:40:59 2006
Date: Tue, 14 Mar 2006 12:40:59 -0500
From: george young <gry@ll.mit.edu>
To: aarni@kymi.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Copying a row within table
Message-Id: <20060314124059.0c354990.gry@ll.mit.edu>
In-Reply-To: <200603140919.49246.aarni@kymi.com>
References: <200603140919.49246.aarni@kymi.com>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
X-Mailer: Sylpheed version 2.0.1 (GTK+ 2.8.0; i686-pc-linux-gnu)
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.101 required=5 tests=[AWL=0.100,
	UNPARSEABLE_RELAY=0.001]
X-Spam-Score: 0.101
X-Spam-Level: 
X-Archive-Number: 200603/220
X-Sequence-Number: 24566

Assuming the sequence in foo is named foo_seq, you could do:

-- You could also select multiple rows here, e.g. foo_id>10, if desired.
create temp table foo_tmp as select * from foo where foo_id=3D2;
alter table foo_tmp add column tmp_seq int default nextval('foo_seq');
-- foo_tmp now *shares* the sequence.
insert into foo select * from foo_tmp;
drop table foo_tmp;

If there's any chance of concurrent update/insert/deletes to foo, you
might should wrap this in a (begin; stuff; commit) transaction.

-- George Young

On Tue, 14 Mar 2006 09:19:49 +0200
Aarni Ruuhim=E4ki <aarni@kymi.com> threw this fish to the penguins:
>=20
> testing=3D# INSERT INTO foo (SELECT * FROM foo WHERE foo_id =3D 2);
> ERROR:  duplicate key violates unique constraint "foo_pkey"
> testing=3D#
>=20
> testing=3D# INSERT INTO foo (foo_1, foo_2, foo_3 ...) (SELECT foo_1, foo_=
2,=20
> foo_3 ... FROM message_table WHERE foo_id =3D 10);
> INSERT 717286 1
> testing=3D#
>=20
> Is there a fast way to copy all but not the PK column to a new row within=
 the=20
> same table so that the new foo_id gets its value from the sequence ?
>=20
> TIA and BR,
>=20
> Aarni
>=20
> --=20
> Aarni Ruuhim=E4ki
> --------------
> This is a bugfree broadcast to you
> from **Kmail**
> on **Fedora Core** linux system
> --------------
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>=20
>                http://archives.postgresql.org
>=20


--=20
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)