Re: [Pypgsql-users] Binary data with postgress

[Gerhard <ger...@gm...>]

* Billy G. Allie <Bil...@mu...> [2002-11-25 16:19 -0500]:
> "Mignon, Laurent" wrote:
> > 
> > How can I do to store binary data like program file, picture, document, etc
> > in postgress?
> > 
> > Thanks for your help.
> > 
> You have a couple of choices:
> 
> 1.  Use a PostgreSQL large object.

If you use large objects with Python 2.2, be sure to upgrade to the
latest version (2.2.2), as earlier 2.2 versions had a bug that will bite
you otherwise.

> 2.  Use a bytea field.
> 
> With later versions of PostgreSQL, it a matter of personal preference as to
> which one you use.  Earlier version had an approximately 8K limit to the
> tuple size, so large objects were were needed for data with a size > 8K.

I was trying to answer this question, too. In the process of which I was
constructing an example using BYTEA. Unfortunately, there seems to be a
bug in pyPgSQL that we should eliminate before (finally, *cough*)
releasing 2.3:

# Database schema used:
# create table test(ba bytea, lo oid);

from pyPgSQL import PgSQL

bindata = "".join([chr(x) for x in range(256)] * 10)

con = PgSQL.connect()

cursor = con.cursor()

# I) BYTEA

# Storing BYTEA:
cursor.execute("insert into test(ba) values (%s)", (PgSQL.PgBytea(bindata),))
last_oid = cursor.oidValue

# Retrieving BYTEA
cursor.execute("select ba from test where oid=%s", (last_oid,))
row = cursor.fetchone()

# Check if input is the same as output
assert bindata == row.ba

con.close()


It looks like the quoting of BYTEAs is wrong, at least when the BYTEA
starts with a chr(0). But I haven't got a real clue where the problem
lies. Though I vaguely remember touching that piece of code once when I
was working on the ARRAY stuff. I hope I didn't introduce the bug then
*blush*.

-- Gerhard