On 11/25/2015 08:56 AM, Francisco Olarte wrote:
Hello Yuriy...
On Wed, Nov 25, 2015 at 4:47 PM, Yuriy Rusinov <yrusinov@xxxxxxxxx> wrote:
I have to transform string, encoded to hexadecimal to text, but if I try to
select encode('Qt is great!', 'hex'); I receive valid and correct results
517420697320677265617421
but if I try to select decode ('517420697320677265617421', 'hex'), I
receive the same string, such as
'\x517420697320677265617421, which way I have to do for valid convert
to/from hexadecimal ?
I seem to recall having answered this a very short time ago, but maybe
it was in the spanish list.
decode/encode are for converting bytes to a string. You need to
convert the string to bytes in a controlled way first ( bear in mind
there are implicit conversions ).
What you want is, given a text:
1.- Convert it to a bytea, in a controlled encoding: convert_to(string
text, dest_encoding name) => bytea
2.- Then encode the bytes in hex: encode(data bytea, format text) => text
then, to revert it you:
3.- Decode the hex string to bytes: decode(string text, format text) => bytea
4.- Convert the bytea, in a controlled encoding, to text:
convert_from(string bytea, src_encoding name) => text
As you see, they are nicelly paired. I see another response which just
does encode , decode+convert_from. This works because the database
does implicit conversions, but I would not recomend it. I cannot try
it because all my databases are UTF-8 but I feel Adrians example would
not work if your database encoding is NOT UTF-8 ( and you use any char
outside of ascii range ).
To follow up:
postgres@postgres=# CREATE DATABASE latin1_db ENCODING 'LATIN1' LC_CTYPE
'C' LC_COLLATE 'C' TEMPLATE template0;
CREATE DATABASE
postgres@latin1_db=# \l latin1_db
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-------------------
latin1_db | postgres | LATIN1 | C | C |
postgres@postgres=# \c latin1_db
You are now connected to database "latin1_db" as user "postgres".
postgres@latin1_db=# \encoding
UTF8
postgres@latin1_db=# select encode('é', 'hex');
encode
--------
e9
(1 row)
postgres@latin1_db=# select decode('e9', 'hex');
decode
--------
\xe9
(1 row)
postgres@latin1_db=# select convert_from('\xe9', 'latin1');
convert_from
--------------
é
(1 row)
Look at the docs of the functions, section 9.4 table 9.7 int the 9.4.5 manual.
If you do it this way, you can also choose the encoding, ie, if you
know your data is latin1 you can convert from/to it and save a few
bytes, or you can convert to/from utf8 an insure you can represent
anything. Then you can encode/decode the bytes in whatever sutis you,
hex, as in yuour eample or base64 if you need to save a few bytes.
Types are there for a reason.
Francisco Olarte.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
