Re: Displaying text appears as hex data

On 8 February 2011 12:45, Michael <postgresql(at)encambio(dot)com> wrote:
>
> Hello Thom,
>
> I sent this accidentally to you directly, here's a copy for the
> list as well.
>
> On Tues., Feb 08, 2011, Thom Brown wrote:
>>On 8 February 2011 10:39, Michael wrote:
>>> 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.
>>>
>>ASCII text?  You mean you wish to translate the binary into ASCII?
>>You can use this:
>>
>>SELECT id, time_stamp, callid, traced_user, convert_from(msg,
>>'SQL_ASCII'), method FROM sip_trace;
>>
>>The reason why it doesn't automatically do this is because since it's
>>binary data, it's up to you to define what its content format is.
>>
> That's understandable and PostgreSQL is doing the right thing, but...
>
> opensips=> SELECT id, time_stamp, callid, traced_user,
>           convert_from(msg, 'SQL_ASCII'), method FROM sip_trace;
> ERROR:  function convert_from(text, unknown) does not exist
> LINE 1: SELECT id, time_stamp, callid, traced_user, convert_from(msg...
>                                                    ^
>
> The arrow in the last line indicates that 'convert_from' is not
> correctly parsed.

My understanding was that your msg column was of type bytea. Is this
not the case? Or is it a different column which needs converting?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935