Re: Postgres blob question - insert from basic.

On Mon, Nov 23, 2009 at 3:49 PM, richard terry <rterry@pacific.net.au> wrote:
> On Tuesday 24 November 2009 01:05:20 Jean-Yves F. Barbier wrote:
>> richard terry a écrit :
>> > I'm confused about permissions to use lo_import/export:
>> ...
>> got the same on command line as a user: must be SU to use lo_import.
>>
>> but why don't U use a BYTEA (upper size limit is 1GB instead of 2GB,
>> but I doubt U have such large objects).
>>
>> on command line, it works as a user:
>> INSERT INTO fruit ('tst', '/tmp/australia_land_of_dragons.png');
>> INSERT 0  1

Careful. The way this INSERT is written, it's inserting the two
strings 'tst' and  '/tmp/australia_land_of_dragons.png' into the table
fruit -- it's not inserting the actual PNG.

> Did you try and retreive the image using this code into another png file and
> try viewing it to verify it was an image you saved? It dosnt' work in my hands
> unfortunately.

If you really want to have your blobs stored in the database, I'd
recommend using a "bytea" column as Jean-Yves suggests -- this way you
won't need superuser privs to insert your data, and you'll avoid the
additional complication of lo_import() only working on files on the
server's filesystem. There might be a way to insert OID records
without using lo_import() and without needing PG superuser privileges,
but I haven't tried. For bytea data, you'll have to find the correct
method in gambas' PG adapter for escaping the binary blob that you'll
be inserting; I'm not familiar with gambas, but as an example in
Python I use psycopg2.Binary(..).

I would seriously consider whether you can get away with just storing
the images on the filesystem instead of inside the database, though.
This topic comes up quite often, see e.g.
http://archives.postgresql.org/pgsql-admin/2002-10/msg00318.php

 Josh