From: george young <gry@ll.mit.edu>
To: aarni@kymi.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Copying a row within table
Date: Tue, 14 Mar 2006 12:40:59 -0500
Message-ID: <20060314124059.0c354990.gry@ll.mit.edu> (raw)
In-Reply-To: <200603140919.49246.aarni@kymi.com>
References: <200603140919.49246.aarni@kymi.com>
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=2;
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äki <aarni@kymi.com> threw this fish to the penguins:
>
> testing=# INSERT INTO foo (SELECT * FROM foo WHERE foo_id = 2);
> ERROR: duplicate key violates unique constraint "foo_pkey"
> testing=#
>
> testing=# INSERT INTO foo (foo_1, foo_2, foo_3 ...) (SELECT foo_1, foo_2,
> foo_3 ... FROM message_table WHERE foo_id = 10);
> INSERT 717286 1
> testing=#
>
> Is there a fast way to copy all but not the PK column to a new row within the
> same table so that the new foo_id gets its value from the sequence ?
>
> TIA and BR,
>
> Aarni
>
> --
> Aarni Ruuhimäki
> --------------
> This is a bugfree broadcast to you
> from **Kmail**
> on **Fedora Core** linux system
> --------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
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: gry@ll.mit.edu, aarni@kymi.com
Subject: Re: Copying a row within table
In-Reply-To: <20060314124059.0c354990.gry@ll.mit.edu>
* 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