Hello Yuriy...
On Wed, Nov 25, 2015 at 4:47 PM, Yuriy Rusinov <yrusinov@gmail.com> 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 ).
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.