> I have a small database (PgSQL 8.0, database encoding UTF8) that folks
> are inserting into via a web form. The form itself is declared
> ISO-8859-1 and the prior to inserting any data, pg_client_encoding is
> set to LATIN1.
Wouldn't it be simpler to have the browser submit the form in utf8 ?
> Most of the high-bit characters are correctly translated from LATIN1 to
> UTF8. So for e-accent-egu I see the two-byte UTF8 value in the database.
>
> Sometimes, in their wisdom, people cut'n'paste information out of MSWord
Argh.
> and put that in the form. Instead of being mapped to 2-byte UTF8
> high-bit equivalents, they are going into the database directly as
> one-byte values > 127. That is, as illegal UTF8 values.
Sometimes you also get HTML entities in the mix. Who knows.
All my web forms are UTF-8 back to back, it just works. Was I lucky ?
Normally postgres rejects illegal UTF8 values, you wouldn't be able to
insert them...
> When I try to dump'n'restore this database into PgSQL 8.2, my data can't
> made the transit.
>
> Firstly, is this "kinda sorta" encoding handling expected in 8.0, or did
> I do something wrong?
Duh ? pg isn't supposed to accept bad unicode data... something
suspicious is going on.
Besides, if it was dumped, it should be reloadable... did pg_dump use a
funky encoding ?
> Secondly, anyone know any useful tools to pipe a stream through to strip
> out illegal UTF8 bytes, so I can pipe my dump through that rather than
> hand editing it?
Yes, use iconv (see man page), it can do this for you quite easily. It's
probably already installed on your system.
Be warned, though, that illegal multibyte characters eat quotes at night
while you aren't looking... unterminated strings are a pain.
You could also load your database with C locale, and have a script select
from the records you wish to convert, and update the rows.
Python has very good Unicode support, should be easy to make such a
script.