Re: Displaying text appears as hex data

On 8 February 2011 22:27, Michael <postgresql(at)encambio(dot)com> wrote:
>
> On Tues., Feb 08, 2011, Thom Brown wrote:
>>On 8 February 2011 19:28, Andreas Kretschmer <akretschmer(at)spamfence(dot)net> wrote:
>>> Thom Brown <thom(at)linux(dot)com> wrote:
>>>> On 8 February 2011 18:45, Andreas Kretschmer <akretschmer(at)spamfence(dot)net> wrote:
>>>>> Michael <postgresql(at)encambio(dot)com> wrote:
>>>>>> 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:
>>>        ^^^^
>>>
>>>>> set:
>>>>>
>>>>> bytea_output = 'escape'
>>>>>
>>>> That won't help as the msg column is actually text... for
>>>> some reason.
>>>
>>> Are you sure? I know that problem from DRUPAL with 9.0.
>>>
>>I asked Michael to confirm the column type of msg and it turned out to
>>be text.  No mention of the lo contrib module, so I guess that may
>>possibly come into play, but I wouldn't know about that.
>>
> By the way if the data is indeed properly stored as text in the
> database, why isn't it printed as text when using a plain SELECT?
> The text data is printed as binary hex instead, what?!
>
> ...but the conversion works of course.

That's why I'm a bit miffed, because you've stored a textual
representation of the binary data in hex. In other words, when it
says '\x494e56495445207369703a', it's stored as that bit of text.
It's not outputting a hexadecimal representation of the data, the
hexadecimal representation is the actual data. So you could have
'\x494ePostgreSQL rules', which obviously wouldn't be valid for
casting to a bytea, but it's perfectly valid text.

The question is, how is that data getting in there? There's probably
binary data with a ::text conversion going on during insert. What you
appear to want is the original textual representation going in as a
text field since you're converting all the time during the SELECT, but
it's being put into binary then cast to text before getting to your
table.

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