I have a question related to this issue:
Now that the locale has changed, it seems that the planner no longer
wants to use the indexes for running LIKE queries on varchar columns
unless I specify varchar_pattern_ops when creating the index. And if I
create the index with varchar_pattern_ops, then the planner won't use
it for = queries.
What's the correct solution to this problem (when using UTF-8 and
lc_collate and lc_ctype are both 1252)? Do I need to create two indexes?
Thanks,
-Chris
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tim Tassonis
Sent: Wednesday, April 23, 2008 12:22
To: Martijn van Oosterhout
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] initdb in 8.3
Martijn van Oosterhout wrote:
> On Wed, Apr 23, 2008 at 04:35:04PM +0200, Tim Tassonis wrote:
>>
>> If specifying a characterset different from the default locale for a
>> database is such a bad idea, why is it possible at all?
>
> It isn't possible, that's the point. What is possible is that client
> can use any encoding they like to talk to the server, but the server
> will store and manage it all in one. What locale C means "I'm an
> encoding wizard and will ensure all my programs can handle all the
> encodings I want to use, because I understand the database will treat
> everything I send as ASCII bytes no matter what encoding the clients
> say it is".
>
>> From how I understand you, if I wanted a postgres server machine
>> supporting databases with different charsets, I'm advised to
initialise
>> one cluster per locale.
>
> If you want to control the *storage* charset, yes. If you just want
> clients to think it's a LATIN9 DB, doing a:
>
> ALTER DATABASE foo SET client_encoding=latin9;
Ok, got it, it's really this setting that's interesting. If I have a
legacy application that defaults to latin1, I can leave the DB to UTF-8
,set the client_encoding to latin1 and then all my selects and inserts
can use latin1, but the data will be stored in utf-8.
Well, that's really all I need, sorry for the confusion.
Thanks a lot
Tim