Thread: 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 !
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:
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 !
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.
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
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
Hello, I'd like to have more comments about the following case: > . 500KB per PDF file; 30 files per year > . PSQL8.3 . struts2.2.3 + mybatis for sql operation . tomcat6 Added more info Solution: ==================== (1) Save pdfs to file system, only point file name in psql8.3 (2) Save oids of pdfs into table (3) Save pdf files as bytea column in psql8.3 Pros and cons for (1), (2), (3), which is the most efficient way? Thanks a lot! Emi
Hello, I'd like to have more comments about the following case: . 500KB per PDF file; 30 files per year . PSQL8.3 Solution: ==================== (1) Save pdfs to file system, only point file name in psql8.3 (2) Save oids of pdfs into table (3) Save pdf files as bytea column in psql8.3 Pros and cons for (1), (2), (3), which is the most efficient way? Thanks a lot! Emi On 05/27/2011 12:45 AM, Jasen Betts wrote: > 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; >
Re: [SQL] Re: 500KB PDF files for postgresql8.3, which is the most efficient way?
From
Samuel Gendler
Date:
On Fri, May 27, 2011 at 9:15 AM, Emi Lu <emilu@encs.concordia.ca> wrote:
Solution:
====================
(1) Save pdfs to file system, only point file name in psql8.3
(2) Save oids of pdfs into table
(3) Save pdf files as bytea column in psql8.3
Pros and cons for (1), (2), (3), which is the most efficient way?
You asked about the "most efficient" but you didn't tell us what you are doing with the pdf files. It really doesn't matter how you store it if you are simply inserting a 500KB object 30 times per year - then it becomes all about convenience. I'd probably rule out solution #1 in that case purely from a maintenance perspective. Storing the PDFs in the db eliminates the maintenance work of ensuring that the files are archived along with the db backups, that file paths stay correct whenever a new db host is provisioned, and storing the files in the db provides transaction-safe file storage. If, on the other hand, you are selecting/updating the rows in question several hundred times per second or more, then you may well want to put some thought into efficiency and, assuming some kind of web access, providing your webserver/caching layer with direct access to the files on the filesystem for service efficiency. But if you are only occasionally accessing the files in question, there's little reason not to put them in the db.
If mybatis can't load the object that references the file without also loading the entire file from the db - and you are using that object for other things that will require frequent loading/storing - then you probably don't want the files in the db. If it can load the object without loading the binary data, by lazily loading that column only when requested, then it doesn't matter. Using BLOBs guarantees that you can access the entire row without loading the binary data if mybatis exposes the blob separately from the rest of the object, but mybatis may be capable of issuing a select without that column and then grabbing that column as needed in order to simulate that in the case of a bytea column, anyway. Internally, a large bytea column is treated similarly to a blob, with any data over a certain size not stored in-line with the rest of the row for efficiency reasons.
Re: [SQL] 500KB PDF files for postgresql8.3, which is the most efficient way?
From
Enrico Weigelt
Date:
* Emi Lu <emilu@encs.concordia.ca> wrote: > Solution: > ==================== > (1) Save pdfs to file system, only point file name in psql8.3 > > (2) Save oids of pdfs into table > > (3) Save pdf files as bytea column in psql8.3 (4) Put them into an object store, eg. venti. cu -- ---------------------------------------------------------------------- Enrico Weigelt, metux IT service -- http://www.metux.de/ phone: +49 36207 519931 email: weigelt@metux.de mobile: +49 151 27565287 icq: 210169427 skype: nekrad666 ---------------------------------------------------------------------- Embedded-Linux / Portierung / Opensource-QM / Verteilte Systeme ----------------------------------------------------------------------