Re: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns

Tom Lane wrote:
> Mario Splivalo <mario.splivalo@megafon.hr> writes:
>> Tom Lane wrote:
>>> Exactly what version of pg_dump are you using?  What I get from pg_dump
>>> doesn't look like that.  Bytea fields with -D look more like this:
>>>
>>> INSERT INTO foo (f1) VALUES ('\\305S\\224\\226\\203)');
> 
>> Yes, I mistakenly used pg8.2 pg_dump, when I use pg3.8 dump I get what 
>> you get.
> 
> I was quoting the output of 8.2.latest pg_dump.  Maybe you have a very
> old subrelease?  But no version of pg_dump would've put an explicit
> cast to bytea in there.

mike@som:~$ pg_dump -V
pg_dump (PostgreSQL) 8.2.4
mike@som:~$

Since I need to have my servers running 24/7 with NO downtime I seldom 
choose to upgrade minor versions, unless there is a major bug that 
affects me. This upgrade from 8.2 to 8.3 is planned, and I have liberty 
of having 3-4 hours of downtime.

> 
>> Btw, what is that S after 305?
> 
> Hex 53 is 'S' I believe.

Still don't get it :) If I have hexadecimal value of C5, that is octal 
305, and I don't get where that S came from.

> 
>> 305 octal is C5 hexadecimal. How 
>> do I enter hexadecimal C5 without UTF encoding errors?
> 
> bytea only supports octal, so \\305 is the way to do it.  The way you
> were doing it was guaranteed to fail on corner cases such as \0 and \
> itself, because you were converting at the string-literal stage not
> byteain().

Ok, that makes sense. Since I just store that data into the database, 
maybe I could store them as strings (varchars), and then do the 
conversion on the client side (java).
Mike