pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCopying a row within table
4+ messages / 3 participants
[nested] [flat]
* Copying a row within table
@ 2006-03-14 07:19 Aarni Ruuhimäki <aarni@kymi.com>
0 siblings, 2 replies; 4+ messages in thread
From: Aarni Ruuhimäki @ 2006-03-14 07:19 UTC (permalink / raw)
To: pgsql-sql
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
--------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Copying a row within table
@ 2006-03-14 17:40 george young <gry@ll.mit.edu>
parent: Aarni Ruuhimäki <aarni@kymi.com>
1 sibling, 0 replies; 4+ messages in thread
From: george young @ 2006-03-14 17:40 UTC (permalink / raw)
To: aarni@kymi.com; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Copying a row within table
@ 2006-03-15 01:11 John DeSoi <desoi@pgedit.com>
parent: Aarni Ruuhimäki <aarni@kymi.com>
1 sibling, 1 reply; 4+ messages in thread
From: John DeSoi @ 2006-03-15 01:11 UTC (permalink / raw)
To: aarni@kymi.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Copying a row within table
@ 2006-03-15 08:35 Aarni Ruuhimäki <aarni@kymi.com>
parent: John DeSoi <desoi@pgedit.com>
0 siblings, 0 replies; 4+ messages in thread
From: Aarni Ruuhimäki @ 2006-03-15 08:35 UTC (permalink / raw)
To: pgsql-sql
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
--------------
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-03-15 08:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-14 07:19 Copying a row within table Aarni Ruuhimäki <aarni@kymi.com>
2006-03-14 17:40 ` george young <gry@ll.mit.edu>
2006-03-15 01:11 ` John DeSoi <desoi@pgedit.com>
2006-03-15 08:35 ` Aarni Ruuhimäki <aarni@kymi.com>
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