Thread: Copying a row within table
Hi people, 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 --------------
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)
On Mar 14, 2006, at 2:19 AM, Aarni Ruuhimäki wrote:
> 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 ?
Here is an example using a plpgsql function:
create or replace function test_duplicate (p_id integer)
returns integer as $$
declarett test%rowtype;
beginselect into tt * from test where id = p_id;tt.id := 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
On Wednesday 15 March 2006 03:11, John DeSoi wrote:
> On Mar 14, 2006, at 2:19 AM, Aarni Ruuhimäki wrote:
> > 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 ?
>
> 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 = p_id;
> tt.id := 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
Hi,
Thank you guys, I'll try these.
Reason I need this is I have a largish product table which stores slightly
different types of products and for some types it is extremely handy to just
clone an existing product and then change the name and other few details
rather than go the time consuming way of creating a new almost identical
product from scratch filling in all the required values and calling all their
check routines in the application. Different types do not use all or the same
colums, some are left null or empty and some have different boolean values in
one or more columns.
Best regards and thanks again,
Aarni
--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core** linux system
--------------