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

Re: BUG #3394: Partial search not working

From: "Murali Doss" <Murali(dot)Doss(at)mphasis(dot)com>
To: "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
Cc: "Douglas Toltzman" <doug(at)oakstreetsoftware(dot)com>,<pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #3394: Partial search not working
Date: 2007-06-19 14:05:13
Message-ID: 7C83A8A6B56D3A478333B1DF47E18586B93140@mpbabgex01.corp.mphasis.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi,

Output of pg_setting in Linux OS

"lc_collate";"C"
"lc_ctype";"C"
"lc_messages";"C"
"lc_monetary";"C"
"lc_numeric";"C"
"lc_time";"C"

Client Encoding

"SQL_ASCII"

Server Encoding

"SQL_ASCII"

And it's same in Solaris and Window OS where the query is returning expected results.

Regards
Murali


-----Original Message-----
From: Alvaro Herrera [mailto:alvherre(at)commandprompt(dot)com] 
Sent: Tuesday, June 19, 2007 7:30 PM
To: Murali Doss
Cc: Douglas Toltzman; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #3394: Partial search not working

Murali Doss wrote:
> I have created new database with encoding as sql_ascii and reloaded the
> data but still its returning 0 rows in Linux.
> 
> SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';

You probably didn't set the appropriate collation, which is the thing
that affects this type of query.  Beware that collation and encoding are
closely related so it still won't work if you choose mismatching
settings.  You probably want C locale as well as SQL_ASCII encoding.

The query is probably poorly thought out though.  Why are you not using
WHERE colname LIKE 'B%'?  How do you know what does your collation
consider as being between B and B~?  Remember, text values are not
necessarily compared using byte comparison.  The C locale uses byte
comparison but I think it's the only one that does so.

-- 
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Duclós)

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-06-19 14:20:13
Subject: Re: BUG #3394: Partial search not working
Previous:From: Alvaro HerreraDate: 2007-06-19 13:59:34
Subject: Re: BUG #3394: Partial search not working

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