Em 17/10/2008, às 18:18, John Skillings escreveu:
Hi all,
I am trying to copy blobs between two tables and need help on the
best way to get this done. My requirement is that the both the
tables maintain their own copy of the large object, instead of
sharing the OID.
I created two tables:
create table table_a
(id bigserial not null,
filename oid);
create table table_b
(id bigserial not null,
filename oid);
In one of the tables, I uploaded a file from the filesystem.
INSERT INTO table_a (id, filename)
VALUES ( nextval('table_a_id_seq'), lo_import('C:/
applications/largeobj.zip'));
The record is inserted, and I verified the record's integrity by:
SELECT lo_export(filename, 'C:/applications/largeobj.zip_copy.zip')
FROM table_a;
Question
----------------
Now to make a copy of the object from table to table_a to table_a.
Currently I am exporting the file from table_a to the file system,
and again doing an import into table_b. However, in a large
application, I find this workaround not practical because of the
volume of the records, and also the size of the file (binary
object). My ideal solution to do an insert of the values from
table_a into table_b directly.
So, what is best way to create a copy of this LOB from table_a to
table_b?
You can copy only the oid, You don't need to have another copy of the
same file in the database, if you copy only the oid you'll have
another reference to the same file.
So a simple
INSERT INTO table_a SELECT * FROM table_b;
will do the trick in your example.
--
Diogo Biazus
diogob@xxxxxxxxx
http://www.softa.com.br
http://www.postgresql.org.br
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
