Обсуждение: Seeking quick way to clone a row, but give it a new pk.
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/
> 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...
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. >
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/
Στις Πέμπτη 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
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
-----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-----
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 $$ declarerec 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.*);returnnew_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