Re: UTF-8 and LIKE vs =

On Aug 23, 2004, at 3:44 PM, Tom Lane wrote:

>> Yes, it means that = is doing the wrong thing!!
>
> I have seen this happen in situations where the strings contained
> character sequences that were illegal according to the encoding that
> the
> locale thought was in force. (It seems that strcoll() will return more
> or less random results in such cases...) In particular, given that you
> have
>
>> LC_COLLATE: en_US.UTF-8
>> LC_CTYPE: en_US.UTF-8
>
> you are at risk if the data is not legal UTF-8 strings.

But is it possible to store non-UTF-8 data in a UNICODE database?

> The real question therefore is whether you have the database encoding
> set correctly --- ie, is it UNICODE (== UTF8)? If not then it may well
> be that Postgres is presenting strings to strcoll() that the latter
> will
> choke on.

The database is UNICODE.

$ psql -U postgres -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
bric | postgres | UNICODE
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)

I plan to dump it, run initdb with LC_COLLATE and LC_CTYPE both set to
"C", and restore the database and see if that helps.

Thanks,

David