Skip site navigation (1) Skip section navigation (2)

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

From: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns
Date: 2008-11-11 11:17:08
Message-ID: 491969B4.5080201@megafon.hr (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

Responses

pgsql-sql by date

Next:From: Mario SplivaloDate: 2008-11-11 11:24:34
Subject: Re: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns
Previous:From: Craig RingerDate: 2008-11-11 03:28:14
Subject: Re: [PERFORM] Can we activate WAL runtime?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group