Re: Copying Blobs between two tables using Insert stmt

Hi Diogo,

Thanks for your response. However, my requirement is to have two separate
copies. Think about table_a being the original copy, and table_b holding
the backup copy.

Inserting the OID from table_a to table_b will not make the backup copy.

Best Regards,

John

On Fri, Oct 17, 2008 at 12:32 PM, Diogo Biazus <diogob(at)gmail(dot)com> wrote:

>
> 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(at)gmail(dot)com
> http://www.softa.com.br
> http://www.postgresql.org.br
>
>