On 8 February 2011 22:27, Michael <postgresql@xxxxxxxxxxxx> wrote: > > On Tues., Feb 08, 2011, Thom Brown wrote: >>On 8 February 2011 19:28, Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> wrote: >>> Thom Brown <thom@xxxxxxxxx> wrote: >>>> On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> wrote: >>>>> Michael <postgresql@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
