Koen Vermeer wrote:
Hi,
I would like to store binary data in a PostgreSQL database. The size of
the data is about 2 to 20 MB and is always stored or retrieved as a
block (i.e., I do not need to get only part of the data). As I
understand, I have two options for storing this data: As BYTEA or as
large objects. As I understand, the disadvantage of the first method is
that I need to write a custom routine to escape some binary values - or
is there some code available to do this? The disadvantage of large
objects is their existence outside of the main database, which may be
problematic when backing up a database. In addition, I need special
routines to store/retrieve the data.
I've used both methods. The only real problem is that none of the
trigger based replication schemes
such as Slony can't deal with large objects.
Depending on what programming language you're using you do *not* need to
escape the binary
data for BYTEA. Using libpq from C/C++ you can pass the binary data
straight into the database. See
PQexecParams :
http://www.postgresql.org/docs/8.3/interactive/libpq-exec.html#LIBPQ-EXEC-MAIN
We use server-side JavaScript here that makes use of that interface so I
can take an image directly
from the web-server and move it into the database with no escape overhead.
My preference : if I don't need the file-like interface to large objects
I'd use BYTEA every time.
Pete
--
http://www.whitebeam.org - JavaScript web application server.
http://www.yellowhawk.co.uk
My two questions are: Is this summary correct? And: Which method should
I choose?
Best,
Koen
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
