Обсуждение: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns

I have this issue:

postgres=# select E'\xc5\x53\x94\x96\x83\x29';
ERROR:  invalid byte sequence for encoding "UTF8": 0xc553
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".
postgres=# show client_encoding ; client_encoding
----------------- UTF8
(1 row)

postgres=# show server_encoding ; server_encoding
----------------- UTF8
(1 row)

postgres=# select version();                                            version 

------------------------------------------------------------------------------------------------ PostgreSQL 8.3.5 on
i686-pc-linux-gnu,compiled by GCC gcc (GCC) 4.2.4 
 
(Ubuntu 4.2.4-1ubuntu3)
(1 row)




On postgres 8.2 this worked:

postgres=# select version();                                                    version 

----------------------------------------------------------------------------------------------------------------
PostgreSQL8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 
 
20061115 (prerelease) (Debian 4.1.1-21)
(1 row)

postgres=# select E'\xc5\x53\x94\x96\x83\x29'; ?column?
---------- S)
(1 row)

postgres=# show client_encoding ; client_encoding
----------------- UTF8
(1 row)

postgres=# show server_encoding ; server_encoding
----------------- UTF8
(1 row)


I'm using the above mentioned string to store data into bytea column. I 
did pg_dump of the database on postgres 8.2, and then tried to restore 
it on postgres 8.3, and I got this error. The actuall line that produces 
error is like this:

INSERT INTO vpn_payins_bitfield (vpn_id, payload_pattern, encription, 
encription_key, charset, amount_width, shop_width, counter_width) VALUES 
(3, E'\\W*(\\w+)(?:\\W+(.*))?', 'RC4', 
E'\xc5\x53\x94\x96\x83\x29'::bytea, 'ABCDEGHIKLMOPTWX', 16, 8, 16);

The error is:
ERROR: invalid byte sequence for encoding "UTF8": 0xc553


Now, I see that I can type: "SELECT E'\xFF'" in pg8.2, but can't do that 
in pg8.3.

So, my question is, how do I specify hexadecimal value of C5 to be 
stored in bytea column, in an INSERT statement?
Mike

Mario Splivalo <mario.splivalo@megafon.hr> writes:
> I have this issue:
> postgres=# select E'\xc5\x53\x94\x96\x83\x29';
> ERROR:  invalid byte sequence for encoding "UTF8": 0xc553

This is expected since the string is not valid as text.

> I'm using the above mentioned string to store data into bytea column. I 
> did pg_dump of the database on postgres 8.2, and then tried to restore 
> it on postgres 8.3, and I got this error. The actuall line that produces 
> error is like this:

> INSERT INTO vpn_payins_bitfield (vpn_id, payload_pattern, encription, 
> encription_key, charset, amount_width, shop_width, counter_width) VALUES 
> (3, E'\\W*(\\w+)(?:\\W+(.*))?', 'RC4', 
> E'\xc5\x53\x94\x96\x83\x29'::bytea, 'ABCDEGHIKLMOPTWX', 16, 8, 16);

Exactly what version of pg_dump are you using?  What I get from pg_dump
doesn't look like that.  Bytea fields with -D look more like this:

INSERT INTO foo (f1) VALUES ('\\305S\\224\\226\\203)');
        regards, tom lane

Tom Lane wrote:
>> I'm using the above mentioned string to store data into bytea column. I 
>> did pg_dump of the database on postgres 8.2, and then tried to restore 
>> it on postgres 8.3, and I got this error. The actuall line that produces 
>> error is like this:
> 
>> INSERT INTO vpn_payins_bitfield (vpn_id, payload_pattern, encription, 
>> encription_key, charset, amount_width, shop_width, counter_width) VALUES 
>> (3, E'\\W*(\\w+)(?:\\W+(.*))?', 'RC4', 
>> E'\xc5\x53\x94\x96\x83\x29'::bytea, 'ABCDEGHIKLMOPTWX', 16, 8, 16);
> 
> Exactly what version of pg_dump are you using?  What I get from pg_dump
> doesn't look like that.  Bytea fields with -D look more like this:
> 
> INSERT INTO foo (f1) VALUES ('\\305S\\224\\226\\203)');

Yes, I mistakenly used pg8.2 pg_dump, when I use pg3.8 dump I get what 
you get. Btw, what is that S after 305? 305 octal is C5 hexadecimal. How 
do I enter hexadecimal C5 without UTF encoding errors?
Mike

Mario Splivalo <mario.splivalo@megafon.hr> writes:
> Tom Lane wrote:
>> Exactly what version of pg_dump are you using?  What I get from pg_dump
>> doesn't look like that.  Bytea fields with -D look more like this:
>> 
>> INSERT INTO foo (f1) VALUES ('\\305S\\224\\226\\203)');

> Yes, I mistakenly used pg8.2 pg_dump, when I use pg3.8 dump I get what 
> you get.

I was quoting the output of 8.2.latest pg_dump.  Maybe you have a very
old subrelease?  But no version of pg_dump would've put an explicit
cast to bytea in there.

> Btw, what is that S after 305?

Hex 53 is 'S' I believe.

> 305 octal is C5 hexadecimal. How 
> do I enter hexadecimal C5 without UTF encoding errors?

bytea only supports octal, so \\305 is the way to do it.  The way you
were doing it was guaranteed to fail on corner cases such as \0 and \
itself, because you were converting at the string-literal stage not
byteain().
        regards, tom lane

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.

--  Richard Huxton Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> Mario Splivalo wrote:
>> That's true, but I'd still like to use hexadecimal notation.

You could use decode():

regression=# select decode('c5a4', 'hex'); decode  
----------\305\244
(1 row)
        regards, tom lane

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

Tom Lane wrote:
> Mario Splivalo <mario.splivalo@megafon.hr> writes:
>> Tom Lane wrote:
>>> Exactly what version of pg_dump are you using?  What I get from pg_dump
>>> doesn't look like that.  Bytea fields with -D look more like this:
>>>
>>> INSERT INTO foo (f1) VALUES ('\\305S\\224\\226\\203)');
> 
>> Yes, I mistakenly used pg8.2 pg_dump, when I use pg3.8 dump I get what 
>> you get.
> 
> I was quoting the output of 8.2.latest pg_dump.  Maybe you have a very
> old subrelease?  But no version of pg_dump would've put an explicit
> cast to bytea in there.

mike@som:~$ pg_dump -V
pg_dump (PostgreSQL) 8.2.4
mike@som:~$

Since I need to have my servers running 24/7 with NO downtime I seldom 
choose to upgrade minor versions, unless there is a major bug that 
affects me. This upgrade from 8.2 to 8.3 is planned, and I have liberty 
of having 3-4 hours of downtime.

> 
>> Btw, what is that S after 305?
> 
> Hex 53 is 'S' I believe.

Still don't get it :) If I have hexadecimal value of C5, that is octal 
305, and I don't get where that S came from.

> 
>> 305 octal is C5 hexadecimal. How 
>> do I enter hexadecimal C5 without UTF encoding errors?
> 
> bytea only supports octal, so \\305 is the way to do it.  The way you
> were doing it was guaranteed to fail on corner cases such as \0 and \
> itself, because you were converting at the string-literal stage not
> byteain().

Ok, that makes sense. Since I just store that data into the database, 
maybe I could store them as strings (varchars), and then do the 
conversion on the client side (java).
Mike

Mario Splivalo wrote:
> 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?
> 

Now, this works:

test1=# select encode(decode('C5', 'hex'), 'hex'); encode
-------- c5
(1 row)


I see that my client encoding has a problem interpreting '\xC5':

test1=# select E'\xC5';
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".

So the only way is to use decode function, right?
Mike

Hi all,

This might be a stupid question, but I wasn't able to find any information on it, so here it goes:
Oracle knows a view which contains information about indexed columns (all_ind_cols), is there something similar
availablein Postgres?
 

I want to be able to create an full overview of each column with the following information:
- index name
- table name the index is on
- column name the index is on
- column position in the index
- sort order

Any help would be much appreciated :)

Regards,

Bart van Houdt
Syfact International B.V.
Database developer

Bart van Houdt wrote:
> Hi all,
> 
> This might be a stupid question, but I wasn't able to find any information on it, so here it goes:
> Oracle knows a view which contains information about indexed columns (all_ind_cols), is there something similar
availablein Postgres?
 
> 
> I want to be able to create an full overview of each column with the following information:
> - index name
> - table name the index is on
> - column name the index is on
> - column position in the index
> - sort order
> 
> Any help would be much appreciated :)
> 

You should check this, for instance:
http://www.postgresql.org/docs/8.3/interactive/monitoring-stats.html#MONITORING-STATS-VIEWS

There is, also, information_schema in postgres, as well as pg_catalog, 
you may wish to check those.
Mike

Cool!
Thx :D

Bart van Houdt
Syfact International B.V.
Database developer

-----Original Message-----
From: Mario Splivalo [mailto:mario.splivalo@megafon.hr] 
Sent: dinsdag 11 november 2008 15:03
To: pgsql-sql@postgresql.org
Cc: Bart van Houdt
Subject: Re: [SQL] Postgres version of all_ind_cols

Bart van Houdt wrote:
> Hi all,
> 
> This might be a stupid question, but I wasn't able to find any information on it, so here it goes:
> Oracle knows a view which contains information about indexed columns (all_ind_cols), is there something similar
availablein Postgres?
 
> 
> I want to be able to create an full overview of each column with the following information:
> - index name
> - table name the index is on
> - column name the index is on
> - column position in the index
> - sort order
> 
> Any help would be much appreciated :)
> 

You should check this, for instance:
http://www.postgresql.org/docs/8.3/interactive/monitoring-stats.html#MONITORING-STATS-VIEWS

There is, also, information_schema in postgres, as well as pg_catalog, 
you may wish to check those.
Mike

am  Tue, dem 11.11.2008, um 14:57:20 +0100 mailte Bart van Houdt folgendes:
> Hi all,

Please, don't hijack other threads.


> 
> This might be a stupid question, but I wasn't able to find any information on it, so here it goes:
> Oracle knows a view which contains information about indexed columns (all_ind_cols), is there something similar
availablein Postgres?
 
> 
> I want to be able to create an full overview of each column with the following information:
> - index name
> - table name the index is on
> - column name the index is on
> - column position in the index
> - sort order
> 
> Any help would be much appreciated :)

Take a look at:
http://www.postgresql.org/docs/current/interactive/catalog-pg-index.html


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net