Re: locale and encoding advice

David Fetter wrote:
I'd go with a C locale and a UTF8 encoding. The downside of
this is
that you may have some trouble importing your old data.

Perhaps you mean this kind of trouble?
psql:<stdin>:142677171: ERROR: invalid byte sequence for
encoding "UTF8": 0x96

This was obtained roughly like this:
$ createdb -E UTF8 newdb
$ pg_dump ... olddb | psql -d newdb -qaf-

The pg_dump prologue has a line:
SET client_encoding = 'SQL_ASCII';

As I said the OP, I need the idiot's guide to encoding... please let me
check my understanding. olddb data is stored on disk in the
server_encoding (SQL_ASCII). When I pg_dump, it's written in the
server_encoding and this is indicated by explicitly setting
client_encoding in the dump prologue. When I load into a UTF8 db,
client_encoding is set to SQL_ASCII (it would otherwise be newdb's UTF8
by default), server_encoding is UTF8, and the backend (?) tries to
translate SQL_ASCII chars to UTF8 chars. In my case, we've choked on
this translation. Is that it?

Here's how I addressed this particular problem:

In looking at the log, I can see which table this is in. It's data I
imported from elsewhere. To find the offending lines, I did:

=> select * from mint \g | perl -ne 'print if $.<3 or m/\x96/' | less

It turns out that only 7 rows are problematic in this way. I did this:

=> update mint set comments=replace(comments,'\x96','-') where
comments~'\x96';

I'm reloading now and we'll see whether I had other problems.

In the future, could I pipe through recode (or other tool?) to do the
translation outside of postgresql? Does anyone have a recommendation to
fix this sort of problem on a larger scale or when a more sophisticated
translation is needed (than my update statement above, I mean).

Thanks for your help.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0