Re: Character encoding and string matches

"Daniel Verite" <daniel(at)manitou-mail(dot)org> writes:
> Richard van den Berg wrote:
>> During the
>> pg_restore, it complained about failing to create several indexes
>> because of duplicates. Inspecting these cases showed that the bogus
>> characters are to blame. It seems that these characters (that are
>> probably not defined in LATIN1) are now turned into wildcards: they will
>> match any other character. Is this an expected result from running a non
>> SQL_ASCII database?

> It should depend on the locale. Can you tell the results of
> `show lc_ctype` and `show lc_collate`?

Yeah, there are a lot of locale definitions out there that seem to
simply fail on unexpected data :-(

If you don't actually need locale-specific sorting or upper()/lower()
behavior, a safe choice is to run with LC_CTYPE and LC_COLLATE set to
'C' while you set the database encoding to whatever you need. This
will support encoding validity checks and transcoding to different
client encodings, but sorting and upper/lower will just use the default
C-locale behavior.

regards, tom lane