From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Michael <postgresql(at)encambio(dot)com> |
Cc: | Postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Displaying text appears as hex data |
Date: | 2011-02-08 13:45:57 |
Message-ID: | AANLkTinkpJG1FYQa4HXCHpo7unTEqK3OXk2hzh-omPtZ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 8 February 2011 13:43, Thom Brown <thom(at)linux(dot)com> wrote:
> On 8 February 2011 13:19, Michael <postgresql(at)encambio(dot)com> wrote:
>>
>> Hello Thom,
>>
>> On Tues., Feb 08, 2011, Thom Brown wrote:
>>>On 8 February 2011 12:45, Michael <postgresql(at)encambio(dot)com> wrote:
>>>> 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.
>>>>>>
>>>>
>>>> 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?
>>>
>> The main developer has confirmed that the msg column is stored
>> as a BLOB, and I'm nearly sure that bytea is used in this case.
>> Yes, the correct column that is appearing in hex is called 'msg.'
>
> Well the function seems to think it's a text field.
>
>> Is the function 'convert_from' that you mentioned compiled into
>> the PostgreSQL server binary, or is it part of the template1 when
>> first created, or something else? It seems I'm missing it, right?
>
> No, you're probably not missing it. It's complaining that a function
> with the given signature (text, unknown), doesn't exist. It will work
> if it matches (bytea, name). The error message being returned is
> saying that the msg field is actually a text field.
>
> Try:
>
> SELECT pg_typeof(msg) FROM sip_trace LIMIT 1;
Or if it really is text format:
SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea,
'SQL_ASCII'::name), method
FROM sip_trace;
But then that's less efficient than storing it as bytea and slower to query.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
From | Date | Subject | |
---|---|---|---|
Next Message | For@ll | 2011-02-08 13:53:54 | Postgresql - recovery.conf |
Previous Message | Thom Brown | 2011-02-08 13:43:47 | Re: Displaying text appears as hex data |