Re: Copying a row within table

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(at)kymi(dot)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)