Re: bytea and character encoding when inserting escaped literals

Asche wrote:
> Hi Lee,
>
>> Thanks for the suggestion. I should have mentioned in my original
>> message that as per your suggestion and the suggestion in the
>> documentation, I have tried escaping the backslashes. When I do this,
>> I get the error:
>>
>> ERROR: invalid input syntax for type bytea
>>
>> I tried also doing
>>
>> INSERT INTO myTable VALUES (...,
>> E'\\x15\\x1C\\x2F\\x00\\x02...'::bytea, ...) ;
>>
>> but get the same errors.
>
> I think i see another problem with your query. You should convert to
> three-digit octal (something like \\001\\002...) not \\x01 (hex?).

Hi Jan,

Thanks, I think I finally see what's happening here (and understand the
docs) - the bytea type has its own string-serialization (escape format)
_separate_ from postgresql's normal string literal escaping. So while
E'\xC0' is postgresql serialization of a string containing whatever
character maps from 0xC0 in the current encoding, that byte cannot
directly go into a bytea. Instead, I need to have a doubly-escaped octal
(specifically) string so that the first escape generates a string like
\000\001\002 which the bytea processor (somewhere) then re-parses as a
sequence of bytes.

Would be nice if the bytea parser understood hex representation too, but
beggars can't be choosers :)

thanks for the help,
Lee

>
> Jan
>
>