Hi,
I'm having a character set problem, and I wonder if anyone here could
sanity check what I'm doing. It might well be that the problem lies
elsewhere.
My database was created with -E UNICODE, and when I do a \l in psql it
is listed as UTF8.
My Java application is receiving data over a socket which is encoded
in UTF8. I'm logging this and it is displaying e.g. Cyrillic or Greek
correctly (using OSX Terminal.app which supports UTF8, tailing the log
with 'less' and the environment variable LESSCHARSET=utf-8.
I'm persisting this data using the latest 8.3 JDBC drivers into
PostgreSQL 8.3.0. I'm not changing the client_encoding (I tried, but I
understand that the JDBC drivers set it to UNICODE anyway, and throw
an error if I attempt to change it to anything else). The data writes
fine, and if I then do a SELECT and a resultSet.getString(x) and write
the output to the log, everything still looks fine. I'm therefore
satisfied that Java + JDBC drivers + PostgreSQL are able to write &
read the data fine. So far so good.
However, if using psql I try to look at the data, it is mangled. If I
try a manual UPDATE via psql using the data cut'n'pasted from my log,
and then look at the data, it reads correctly. Therefore I know that
psql is capable of reading and writing UTF8 data correctly. Also, the
client application that reads from my database is Perl, and this also
retrieves mangled data; we've tried writing and reading directly from
Perl, and in this case reviewing the data in psql looks normal.
The conclusion I've reached is that Java + JDBC is not actually
persisting the data in UTF-8; is that correct or am I wildly off base,
and if it is correct then is there anything I can do about it?!
Many thanks,
Ant.
