Re: Convert from hex to string - Mailing list pgsql-general

Mail pingpong day. ;-)

On Wed, Nov 25, 2015 at 7:27 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

> postgres@latin1_db=# \encoding
> UTF8

This does not matter, as you are sending/receiving hex data, and the
encoding done when sending query results to you gets reverted when you
send queries back to the server.


> postgres@latin1_db=# select convert_from('\xe9', 'latin1');
>  convert_from
> --------------
>  é
> (1 row)

This DOES matter, you've had to change the query for it to work, you
had to look up ( or know beforehand ) the database encoding and change
it accordingly. . I mean, when you do encode('text',....) what you are
really asking for is encode( implicit_text_to_bytea_conversion(text),
....), so you save one step, but them you have to change your query to
the 'latin1' encoding you discovered. This is what I meant, you had to
look at the database properties. But if you do an explicit
convert_form with any encoding capable of representing all your data,
like utf-8 which can represent anything,  the database encoding does
not matter. And it should not, proper code should work with any
database encoding. Even more, I can do encode(convert_to(utf8)) in a
latin1 database conecting with any encoding, then send the hex to
convert_from(decode(),utf8) to an ebcdic database use another encoding
( Of course I may need to transcode the hex, but not to the ebcdic,
but to the client encoding been used in teh second case ), ant it
would work as long as all the characters In the source data are
representable in the destination database ( if they are not you are
out luck in any scheme you may think off ).

You cannot encode generically an string to hex unless you define an
encoding. How do you encode '1', "31" or "F1" ? or maybe "0031" or
"3100"? You can do a quick hack, but normally what you want is first
to encode a sequence of characters to a sequence of bytes and then
hex-encode that, as nearly everybody uses the same conversion for
hex-encoding a byte sequence. This means you can have a '0' in a
ebcdic database, transform it to to [0x30] byte array, encode this as
"30" and then transform the later to 00 30 00 10 because you are using
UTF16-BE wire encoding. Encoding is tricky enough without relying on
implicit convertion or on a character being the same as a byte.

Francisco Olarte.