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