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

On Wed, Nov 25, 2015 at 6:22 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
>> 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
>
>
> Can't this be shortened to:
>
> aklaver@test=> select convert_to( 'é', 'latin1');
>  convert_to
> ------------
>  \xe9
> (1 row)
>
> aklaver@test=> select convert_from( '\xe9', 'latin1');
>  convert_from
> --------------
>  é
>
> since convert_to() outputs bytea and convert_from() accepts bytea?

Yes, but he originally asked to convert to hex, I assumed a hex string.

Note you are using hex because this is th default encoding for bytea
output in the database, as the database needs to convert everything to
text to send it to psql to display it and psql sends text which the
database needs to convert to operate.

But if you are using something like, say, java, you would need to bind
convert_to output and convert_from input to a byte[] ( although maybe
jdbc is sending/receivine hex strings in the client encoding, the wire
protocol is transparent to you ), which you can then print however you
like, if you want to bind String with hex encoded data you nide the
encode/decode steps.


>> 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 ).
>
> If you are doing all this in the same database I am not sure how the above
> applies?

You explicitly used convert_from with UTF8, if the database was latin
1 or ebcdic you would have an encoding mismatch, as the text output
routines will convert the input text to bytea using that encoding.

> Would you not just use the database encoding for the src_encoding?

I do not quite understand the question, if you want your
encode-decode-convert_from towork, yes, you use the database encoding
in convert from, but I did not see a 'show client encoding' or similar
thing in your sample.

Anyway, I was assuming the hex conversion was needed for something
more complex than just pasting the data, for that anything can go,
including a \g | perl -pe 'unpack H*'. So I tried to show how the data
flows without relying on any implicit conversion, the
convert_to+encode => decode+convert_from works in any client encoding,
even in a thing like ebcdic.

Francisco Olarte.