Re: storing binary data

Cristóvão Dalla Costa wrote:

> Hi,
> 
> I'm using libpq to interface my program to postgresql, and I need to store
> some binary data (around 30-40 bytes per element). I'm not sure of the best
> way to do so, since blobs seem awkward. How would you do it? How would you
> insert it in a text field?
> 
> Thanks all,
> 
> Cristovao
> 

I recommend BYTEA datatype for this. It works in a similar manner to 
TEXT, but it is designed for binary data. You will need to escape 
certain values on INSERT and unescape on SELECT. See:

http://fts.postgresql.org/db/mw/msg.html?mid=1042181

for a recent post on this.

Hope this helps,

-- Joe

p.s.
Note that in 7.2 (in beta now) there is a function in libpq specifically 
for escaping BYTEA input strings (PQescapeBytea -- see fe-exec.c):

/* *    PQescapeBytea    - converts from binary string to the *    minimal encoding necessary to include the string in
anSQL *    INSERT statement with a bytea type column as the target. * *    The following transformations are applied *
 '\0' == ASCII  0 == \\000 *    '\'' == ASCII 39 == \' *    '\\' == ASCII 92 == \\\\ */
 
unsigned char *
PQescapeBytea(unsigned char *bintext, size_t binlen, size_t *bytealen)