Обсуждение: copy command and blobs

hello,

i have a table with OID column.. I want to use the copy command to insert bunch of rows (1 million).
but iam unable to specify the correct format for the oid type (i have .jpg files to be stored in this column)..

I tried giving the path to the file, lo_import('pathto file').. appreciate any tips.. i did search the archives , but couldnt find any answer on this

Thanks again

Madhu Ramachandran wrote:
> hello,
>
> i have a table with OID column.. I want to use the copy command to
> insert bunch of rows (1 million).
> but iam unable to specify the correct format for the oid type (i have
> .jpg files to be stored in this column)..
Huh? oid is a keyword, an automatically generated row id, and is
deprecated. You shouldn't be doing anything with it, much less copying it.


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Mladen Gogala <mladen.gogala@vmsinfo.com> writes:
> Madhu Ramachandran wrote:
>> i have a table with OID column.. I want to use the copy command to
>> insert bunch of rows (1 million).
>> but iam unable to specify the correct format for the oid type (i have
>> .jpg files to be stored in this column)..

> Huh? oid is a keyword, an automatically generated row id, and is
> deprecated. You shouldn't be doing anything with it, much less copying it.

I think what the OP actually means is he's thinking of importing some
images as large objects, then storing their OIDs in a user (not system)
column of type oid.  COPY can't be used for that though.

It might be better to use a bytea column, if you're willing to deal with
bytea's weird escaping rules.

            regards, tom lane

i was looking at 

On Fri, Jan 21, 2011 at 5:10 PM, Madhu Ramachandran <iammadhu@gmail.com> wrote:
> i was looking at
> http://www.postgresql.org/files/documentation/books/aw_pgsql/node96.html
> when they talk about using OID type to store large blobs (in my case .jpg
> files )

It's probably worth noting that that document is 9 years old.  It
might be worth reading something a little more up-to-date.  Perhaps:

http://www.postgresql.org/docs/current/static/largeobjects.html

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

A bit late to respond but better than never!

As of my latest testing in 8.3, I've found that the lo_* functions while adequate are a bit slow.  Our implemented alternative that leverages pg_read_file() is significantly faster.  I believe it is because pg_read_file() tells the database to go straight to the file system rather than through the client connection.  From memory, I seem to recall this being about 20% faster than the lo_* option or simple INSERTs.

The downside to pg_read_file() is that the file must be 1) on the same system as the database and 2) must be under the $PGDATA directory.  We opted to create a directory $PGDATA/public with proper system-side permissions but open enough to allow the database owner to read the files.

For example,
postgres=# select pg_read_file('public/a_file', 0, (pg_stat_file('postgresql.conf')).size);

We use this method in conjunction with additional checks to store files in tables governed by the MD5 hash of the file to prevent duplication.

HTH.
Greg