pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcopy record?
5+ messages / 4 participants
[nested] [flat]
* copy record?
@ 2011-05-26 20:23 Gauthier, Dave <dave.gauthier@intel.com>
2011-05-26 21:04 ` Re: copy record? Gauthier, Dave <dave.gauthier@intel.com>
2011-05-27 15:39 ` Re: copy record? Merlin Moncure <mmoncure@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Gauthier, Dave @ 2011-05-26 20:23 UTC (permalink / raw)
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Hi:
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: copy record?
2011-05-26 20:23 copy record? Gauthier, Dave <dave.gauthier@intel.com>
@ 2011-05-26 21:04 ` Gauthier, Dave <dave.gauthier@intel.com>
2011-05-26 21:18 ` Re: copy record? Bosco Rama <postgres@boscorama.com>
1 sibling, 1 reply; 5+ messages in thread
From: Gauthier, Dave @ 2011-05-26 21:04 UTC (permalink / raw)
To: Gauthier, Dave <dave.gauthier@intel.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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-owner@postgresql.org [mailto:pgsql-general-owner@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:
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: copy record?
2011-05-26 20:23 copy record? Gauthier, Dave <dave.gauthier@intel.com>
2011-05-26 21:04 ` Re: copy record? Gauthier, Dave <dave.gauthier@intel.com>
@ 2011-05-26 21:18 ` Bosco Rama <postgres@boscorama.com>
2011-05-27 04:45 ` Re: copy record? Jasen Betts <jasen@xnet.co.nz>
0 siblings, 1 reply; 5+ messages in thread
From: Bosco Rama @ 2011-05-26 21:18 UTC (permalink / raw)
To: Gauthier, Dave <dave.gauthier@intel.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: copy record?
2011-05-26 20:23 copy record? Gauthier, Dave <dave.gauthier@intel.com>
2011-05-26 21:04 ` Re: copy record? Gauthier, Dave <dave.gauthier@intel.com>
2011-05-26 21:18 ` Re: copy record? Bosco Rama <postgres@boscorama.com>
@ 2011-05-27 04:45 ` Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 5+ messages in thread
From: Jasen Betts @ 2011-05-27 04:45 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On 2011-05-26, Bosco Rama <postgres@boscorama.com> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: copy record?
2011-05-26 20:23 copy record? Gauthier, Dave <dave.gauthier@intel.com>
@ 2011-05-27 15:39 ` Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Merlin Moncure @ 2011-05-27 15:39 UTC (permalink / raw)
To: Gauthier, Dave <dave.gauthier@intel.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Thu, May 26, 2011 at 3:23 PM, Gauthier, Dave <dave.gauthier@intel.com> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-05-27 15:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-26 20:23 copy record? Gauthier, Dave <dave.gauthier@intel.com>
2011-05-26 21:04 ` Gauthier, Dave <dave.gauthier@intel.com>
2011-05-26 21:18 ` Bosco Rama <postgres@boscorama.com>
2011-05-27 04:45 ` Jasen Betts <jasen@xnet.co.nz>
2011-05-27 15:39 ` Merlin Moncure <mmoncure@gmail.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