Re: Displaying text appears as hex data

On 8 February 2011 10:39, Michael <postgresql(at)encambio(dot)com> wrote:
>
> Hello list,
>
> I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
> as BLOB 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.

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.

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