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

Tom Lane wrote:
> Mario Splivalo <mario(dot)splivalo(at)megafon(dot)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(at)som:~$ pg_dump -V
pg_dump (PostgreSQL) 8.2.4
mike(at)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