Selecting data from bytea field in 8.3

Started by Tomasz Rejmanalmost 17 years ago2 messagesgeneral
Jump to latest
#1Tomasz Rejman
tr@ideo.pl

Hi there.
Few days ago I have changed my postgreSQL to 8.3 version and I have problem
with bytea fields. Let me show you an example:

CREATE TABLE testtable
(
test bytea
);
insert into testTable (test) VALUES
(E'\304\205\304\207\305\274\303\263\305\202wjfdsafdsa');
select encode(test, 'escape') from testTable;

In the previous version last Select will display exact characters in UTF8
encoding (some polish letters with dot-s and comma-s on them), but in 8.3
i'm getting an escaped string with byte codes (like this one in Insert
querry)

(correct data should be like that:
select E'\304\205\304\207\305\274\303\263\305\202wjfdsafdsa';
)

What should I do to tell the postgreSQL to unescape that data?
because I cant add an E letter to column name.
Something like that:

Select E test;

btw. I know that bytea field should be used for binary data, but this is
only an example.
I'm using this type of field in my functions to encrypt/decrypt some data
and i need to use set_byte()/get_byte() functions.

--
Tomasz Rejman

#2Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Tomasz Rejman (#1)
Re: Selecting data from bytea field in 8.3

Tomasz Rejman wrote:

Few days ago I have changed my postgreSQL to 8.3 version and I have problem
with bytea fields. Let me show you an example:

CREATE TABLE testtable
(
test bytea
);
insert into testTable (test) VALUES
(E'\304\205\304\207\305\274\303\263\305\202wjfdsafdsa');
select encode(test, 'escape') from testTable;

In the previous version last Select will display exact characters in UTF8
encoding (some polish letters with dot-s and comma-s on them), but in 8.3
i'm getting an escaped string with byte codes (like this one in Insert
querry)

(correct data should be like that:
select E'\304\205\304\207\305\274\303\263\305\202wjfdsafdsa';
)

What should I do to tell the postgreSQL to unescape that data?

select convert_from(test, 'UTF8') from testTable;

Yours,
Laurenz Albe