Re: Displaying text appears as hex data

Hello Karsten,

On Tues., Feb 08, 2011, Karsten Hilbert wrote:
>On 8 February 2011 10:39, Michael wrote:
>> I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
>> as BLOB
>
>I take it you mean BYTEA.
>
That's probably correct, yes.

>> and PostgreSQL is displaying it in hex format like so:
>>
>> $ TERM=vt100 /pfx/bin/psql opensips opensips
>> psql (9.0.2)
>> Type "help" for help.
>>
>> opensips=> select * from sip_trace;
>>  id | time_stamp | callid | traced_user | msg | method | ...
>>  1234 | 2011-02-03 | ... | | \x494e56495445207369703a...
>>
>> Others have said that when they use MySQL, the exact SQL command
>> as above results in ASCII text rather than hexadecimal, and this
>> is my goal as well.
>>
>You might attempt to apply decode(column, 'hex') to the
>relevant column.
>
I had tried that before, and here's the result:

opensips=> SELECT id, time_stamp, callid, traced_user,
           decode(msg, 'hex'), method FROM sip_trace;
ERROR:  invalid hexadecimal digit: "\"

I don't understand this. Isn't it PostgreSQL that stores the BYTEA
values and then displays them in hex, indicating this by prepending
the '\x' backslash ex? Or if the '\x' is actually stored, then why
and who is doing that?

Any idea?

Thanks,
Michael