Re: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns

Richard Huxton wrote:
> Mario Splivalo wrote:
>> Richard Huxton wrote:
>>> Mario Splivalo wrote:
>>>> I have this issue:
>>>>
>>>> postgres=# select E'\xc5\x53\x94\x96\x83\x29';
>>>> ERROR: invalid byte sequence for encoding "UTF8": 0xc553
>>> I think you want to be using octal escapes. That's text you're
>>> generating above.
>>>
>>> CREATE TABLE bytea_test (b bytea);
>>> INSERT INTO bytea_test (b) VALUES (E'\\305\\123\\224\\226');
>>> SELECT * FROM bytea_test;
>>> b
>>> ---------------
>>> \305S\224\226
>>> (1 row)
>> That's true, but I'd still like to use hexadecimal notation. Manual
>> states that I could say '\xC5', but then I get those encoding errors.
>
> I think you're reading the "text" rather than "bytea" part of the manual.
>
> 4.1.2.1. String Constants
> "...and \xhexdigits, where hexdigits represents a hexadecimal byte
> value. (It is your responsibility that the byte sequences you create are
> valid characters in the server character set encoding.)"
>
> No mention of hex in the bytea section of the manual.
>

Hm, you're right. I guess that part of manual confuses me. Nevertheless,
I'd still like to be able to enter hexadecimal values to the bytea
fields. As Tom Lane suggested, I can do this:

test1=# select decode('C5', 'hex');
decode
--------
\305
(1 row)

But, if I want it other way around, I get the error:

test1=# select envode(E'\305', 'hex');
ERROR: invalid byte sequence for encoding "UTF8": 0xc5
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".
test1=#

Is there a way to circumvent encoding when dealing with binary data? Or
am I completely confused here and have no clue what am I talking about?

Mike