Re: [GENERAL] copy record?
On Thu, May 26, 2011 at 3:23 PM, Gauthier, Dave wrote: > Hi: > > > > From within a perl/DBI script, I want to be able to make a copy of a record > in a table, changing only the value of the primary key. I don't know in > advance what all the columns are, just the table name. > > > > I suppose I could pull the column names for the table from the metadata, > query the table/record for the values to copy, build an insert statement > from all of that and run it. But is there a simpler, more elegant way to do > this? there's a very easy way using the composite type method as long as you know which field(s) are the primary key -- especially if it's say the first column and an integer. postgres=# \d foo Table "public.foo" Column | Type | Modifiers +-+--- a | integer | b | integer | Indexes: "foo_a_b_idx" btree (a, b) postgres=# select foo from foo limit 1; foo --- (1,1) (1 row) change 1 -> 2 textually, cast the text back to the composite and pass it back in insert into foo select (($$(2,1)$$::foo).*); merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy record?
On 2011-05-26, Bosco Rama wrote: > select * into temp table foo from maintable where primcol=123; > update foo set primcol = 456; > insert into maintable select * from foo; > > You also may need this is if you intend to use the same sequence of > calls on within the same session: > > drop table foo; Yet another way to do the same thing: begin; create temportary table foo on commit drop as select * from maintable where primcol=123; update foo, set primcol=456; insert into maintable select * from foo; commit; -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy record?
Gauthier, Dave wrote: > Well, I found a better way, but still open to suggestions. > > This is what I have so far... > > create temporary table foo as select * from maintable where 1-0; -- Credit 4 > this goes to a post in the PG archives > insert into foo (select * from maintable where primcol=123); > update foo, set primcol=456; > insert into maintable (select * from foo); This alleviates the need to trick the back-end using 'where 1-0' and also does the insert, all in one go. select * into temp table foo from maintable where primcol=123; update foo set primcol = 456; insert into maintable select * from foo; You also may need this is if you intend to use the same sequence of calls on within the same session: drop table foo; HTH Bosco. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy record?
Well, I found a better way, but still open to suggestions. This is what I have so far... create temporary table foo as select * from maintable where 1-0; -- Credit 4 this goes to a post in the PG archives insert into foo (select * from maintable where primcol=123); update foo, set primcol=456; insert into maintable (select * from foo); From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Thursday, May 26, 2011 4:24 PM To: pgsql-general@postgresql.org Subject: [GENERAL] copy record? Hi: >From within a perl/DBI script, I want to be able to make a copy of a record in >a table, changing only the value of the primary key. I don't know in advance >what all the columns are, just the table name. I suppose I could pull the column names for the table from the metadata, query the table/record for the values to copy, build an insert statement from all of that and run it. But is there a simpler, more elegant way to do this? Thanks for any help !
[GENERAL] copy record?
Hi: >From within a perl/DBI script, I want to be able to make a copy of a record in >a table, changing only the value of the primary key. I don't know in advance >what all the columns are, just the table name. I suppose I could pull the column names for the table from the metadata, query the table/record for the values to copy, build an insert statement from all of that and run it. But is there a simpler, more elegant way to do this? Thanks for any help !
