Skip site navigation (1) Skip section navigation (2)

Re: initdb in 8.3

From: "Christopher Condit" <condit(at)sdsc(dot)edu>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Re: initdb in 8.3
Date: 2008-04-23 17:57:55
Message-ID: 845AADAC1106E44996327D62097E4C6B01025F71@et.ad.sdsc.edu (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: Karsten HilbertDate: 2008-04-23 18:10:07
Subject: Re: How to modify ENUM datatypes?
Previous:From: Roberts, JonDate: 2008-04-23 17:56:23
Subject: ROLAP visualization tool recommendations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group