pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSeeking quick way to clone a row, but give it a new pk.
8+ messages / 6 participants
[nested] [flat]
* Seeking quick way to clone a row, but give it a new pk.
@ 2007-02-07 22:21 Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 3 replies; 8+ messages in thread
From: Bryce Nesbitt @ 2007-02-07 22:21 UTC (permalink / raw)
To: pgsql-sql
I need to create some nearly identical copies of rows in a complicated
table.
Is there a handy syntax that would let me copy a existing row, but get a
new primary key for the copy? I'd then go in an edit the 1 or 2
additional columns that differ. The duplicate would be in the same
table as the original.
This would save me a bunch of typing. Can it be done?
----
Visit http://www.obviously.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking quick way to clone a row, but give it a new pk.
@ 2007-02-08 00:17 Philip Hallstrom <postgresql@philip.pjkh.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
2 siblings, 1 reply; 8+ messages in thread
From: Philip Hallstrom @ 2007-02-08 00:17 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
> I need to create some nearly identical copies of rows in a complicated
> table.
>
> Is there a handy syntax that would let me copy a existing row, but get a
> new primary key for the copy? I'd then go in an edit the 1 or 2
> additional columns that differ. The duplicate would be in the same
> table as the original.
>
> This would save me a bunch of typing. Can it be done?
INSERT INTO mytable SELECT * FROM mytable WHERE pk = 123;
Or something close to that... I suspect if you changed the '*' to the
columns you wanted you could also work in the other columns you want to
change as well...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking quick way to clone a row, but give it a new pk.
@ 2007-02-08 07:16 Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 0 replies; 8+ messages in thread
From: Bryce Nesbitt @ 2007-02-08 07:16 UTC (permalink / raw)
To: pgsql-sql; +Cc: Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com>
I have a table that describes some properties. It is logically
equivalent to:
pk userid favorite_color time_zone count
122 100 red Pacific 7
145 101 blue Eastern 7
For various reasons I need to manually add a few rows to this table.
Manually copying one users row, then editing it, would be easier than
starting from scratch.
Hiltibidal, Robert wrote:
> What are you trying to accomplish?
>
> The more normalized a database is the faster it operates, the more
> efficient it will be. What you are describing creates a lot of in
> efficiencies within a database.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking quick way to clone a row, but give it a new pk.
@ 2007-02-08 07:19 Bryce Nesbitt <bryce1@obviously.com>
parent: Philip Hallstrom <postgresql@philip.pjkh.com>
0 siblings, 2 replies; 8+ messages in thread
From: Bryce Nesbitt @ 2007-02-08 07:19 UTC (permalink / raw)
To: pgsql-sql; +Cc: Philip Hallstrom <postgresql@philip.pjkh.com>
Philip Hallstrom wrote:
>> I need to create some nearly identical copies of rows in a complicated
>> table.
>>
>> Is there a handy syntax that would let me copy a existing row, but get a
>> new primary key for the copy? I'd then go in an edit the 1 or 2
>> additional columns that differ. The duplicate would be in the same
>> table as the original.
>>
>> This would save me a bunch of typing. Can it be done?
>
> INSERT INTO mytable SELECT * FROM mytable WHERE pk = 123;
>
> Or something close to that... I suspect if you changed the '*' to the
> columns you wanted you could also work in the other columns you want
> to change as well...
But that will violate the unique primary key constraint:
insert into xx_plan_rule select * from xx_plan_rule where rule_id=9;
ERROR: duplicate key violates unique constraint "xx_plan_rule_pkey"
--
----
Visit http://www.obviously.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking quick way to clone a row, but give it a new pk.
@ 2007-02-08 07:58 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 0 replies; 8+ messages in thread
From: Achilleas Mantzios @ 2007-02-08 07:58 UTC (permalink / raw)
To: pgsql-sql
Στις Πέμπτη 08 Φεβρουάριος 2007 09:19, ο/η Bryce Nesbitt έγραψε:
> Philip Hallstrom wrote:
> >> I need to create some nearly identical copies of rows in a complicated
> >> table.
> >>
> >> Is there a handy syntax that would let me copy a existing row, but get a
> >> new primary key for the copy? I'd then go in an edit the 1 or 2
> >> additional columns that differ. The duplicate would be in the same
> >> table as the original.
> >>
> >> This would save me a bunch of typing. Can it be done?
> >
> > INSERT INTO mytable SELECT * FROM mytable WHERE pk = 123;
> >
> > Or something close to that... I suspect if you changed the '*' to the
> > columns you wanted you could also work in the other columns you want
> > to change as well...
>
> But that will violate the unique primary key constraint:
>
> insert into xx_plan_rule select * from xx_plan_rule where rule_id=9;
> ERROR: duplicate key violates unique constraint "xx_plan_rule_pkey"
If you have that requirement often i would recommend writing
a program taking the tablename,id as args, read DB metadata and act
accordingly.
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking quick way to clone a row, but give it a new pk.
@ 2007-02-08 09:32 Aarni Ruuhimäki <aarni@kymi.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 0 replies; 8+ messages in thread
From: Aarni Ruuhimäki @ 2007-02-08 09:32 UTC (permalink / raw)
To: pgsql-sql; +Cc: Philip Hallstrom <postgresql@philip.pjkh.com>
On Thursday 08 February 2007 09:19, Bryce Nesbitt wrote:
> >
> > INSERT INTO mytable SELECT * FROM mytable WHERE pk = 123;
> >
> > Or something close to that... I suspect if you changed the '*' to the
> > columns you wanted you could also work in the other columns you want
> > to change as well...
>
> But that will violate the unique primary key constraint:
>
> insert into xx_plan_rule select * from xx_plan_rule where rule_id=9;
> ERROR: duplicate key violates unique constraint "xx_plan_rule_pkey"
It will, because you are copying all columns, including the pk.
Try:
INSERT INTO mytable (colname_1, colname_2, colname_3)
SELECT (colname_1, colname_2, colname_3)
FROM mytable WHERE pk = 123;
BR,
--
Aarni Ruuhimäki
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking quick way to clone a row, but give it a new pk.
@ 2007-02-08 16:18 Greg Sabino Mullane <greg@turnstep.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
2 siblings, 0 replies; 8+ messages in thread
From: Greg Sabino Mullane @ 2007-02-08 16:18 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> I need to create some nearly identical copies of rows in
> a complicated table.
>
> Is there a handy syntax that would let me copy a existing row,
> but get a new primary key for the copy?
http://people.planetpostgresql.org/greg/index.php?/archives/45-Making-a-copy-of-a-unique-row.html
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200702081114
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFFy0xcvJuQZxSWSsgRA8vFAJsHMFhngWGCSSi8okO9j9H0++hajgCgrUz+
aKTnhaHuQHv1qetAmPt/ufM=
=HDF8
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking quick way to clone a row, but give it a new pk.
@ 2007-02-09 00:09 John DeSoi <desoi@pgedit.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
2 siblings, 0 replies; 8+ messages in thread
From: John DeSoi @ 2007-02-09 00:09 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
A pl/pgsql function can do this easily. Something like this (not
tested):
create or replace function dup_my_table(old_key text, new_key text)
returns text as
$$
declare
rec my_table;
begin;
select into rec * from my_table where key_field = old_key;
rec.key_field = new_key;
insert into my_table values (rec.*);
return new_key;
end;
$$ language plpgsql;
On Feb 7, 2007, at 4:21 PM, Bryce Nesbitt wrote:
> I need to create some nearly identical copies of rows in a complicated
> table.
>
> Is there a handy syntax that would let me copy a existing row, but
> get a
> new primary key for the copy? I'd then go in an edit the 1 or 2
> additional columns that differ. The duplicate would be in the same
> table as the original.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-02-09 00:09 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-07 22:21 Seeking quick way to clone a row, but give it a new pk. Bryce Nesbitt <bryce1@obviously.com>
2007-02-08 00:17 ` Philip Hallstrom <postgresql@philip.pjkh.com>
2007-02-08 07:19 ` Bryce Nesbitt <bryce1@obviously.com>
2007-02-08 07:58 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-02-08 09:32 ` Aarni Ruuhimäki <aarni@kymi.com>
2007-02-08 16:18 ` Greg Sabino Mullane <greg@turnstep.com>
2007-02-09 00:09 ` John DeSoi <desoi@pgedit.com>
2007-02-08 07:16 Re: Seeking quick way to clone a row, but give it a new pk. Bryce Nesbitt <bryce1@obviously.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