Re: Re: [GENERAL] Using BLOBs with PostgreSQL

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> "bytea for binary data with escapes"??
>> 
>> What would those escapes look like?  I could not find any escape
>> that would put a NULL character into a bytea field.

> 'a\\0b' put a, NULL, b.  You need double-backslash to get one backslash
> to the backend.

Looking at byteain, it's clear that you must write 3 octal digits,
so the correct incantation isa\000b
in COPY input data, ora\\000b
in a quoted literal in an SQL INSERT statement (since the parser will
eat one level of backslash before the type-specific converter gets it).

This strikes me as not too unreasonable for ASCII conversion of bytea.

I believe that it should be possible to retrieve an unconverted form
of a bytea value via a BINARY CURSOR, though I haven't tried it myself.

You could also do I/O without conversion using COPY BINARY, but only if
you are superuser, and even then the surrounding file syntax is weird
and undocumented.  COPY BINARY across stdin/stdout (which'd be needed
for non-superuser usage) doesn't work.

I don't think there's anything particularly wrong with toasted bytea
as a storage mechanism for non-textual BLOBs, but we'd certainly need
to do something about the COPY situation to make storing random binary
data in a bytea anything except an exercise in masochism.  Also it'd
be nice if bytea wasn't quite so impoverished operator-wise.
        regards, tom lane