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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Tim Tassonis Sent: Wednesday, April 23, 2008 12:22 To: Martijn van Oosterhout Cc: pgsql-general@xxxxxxxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
