Re: String comparision in PostgreSQL

On Thu, Aug 30, 2012 at 9:34 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Aug 30, 2012 at 6:09 AM, F. BROUARD / SQLpro
> <sqlpro(at)club-internet(dot)fr> wrote:
>> Le 30/08/2012 12:45, Craig Ringer a écrit :
>>
>>
>>> That's my understanding, but I don't know which other database systems
>>> you're talking about because you've never specifically named any.
>>>
>> In his primary post he talk about SQL Server, Sybase and MySQL wich does
>> good jobs with collation....
>>
>> Almost a majority of RDBMS have collation support wich is very important for
>> non english languages, that represents about 90% of the planet languages !
>>
>> This feature has always been a "black spot" in PG, and the most important
>> topic to discourage to use it in professionnal applications.
>
> Citations please.
>
> PostgreSQL has excellent collation support.
> http://www.postgresql.org/docs/9.1/static/collation.html
>
> Sybase performance on upper() case insensitive searchs:
> http://stackoverflow.com/questions/81268/case-insensitive-search-on-sybase
>
> MySQL case insensitive searchs rely on indexing upper or lower
> functions just like PostgreSQL and use seq scans for collation induced
> case insensitive searchs:
> http://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search
>
> Further PostgreSQL has the citext type:
> http://www.postgresql.org/docs/9.1/static/citext.html
> Which can be handy for case insensitive searches but can ONLY do case
> insensitive stuff.

Yeah. In particular, lower() approaches for case insensitive
searching have always worked and IMSNHO remain the best way.
Expression based searching and indexing is a real strong point for
postgres and is the 'go to' method for solving a broad array of
problems. The fairest complaint you can make is that historically
you've had to ditch performance to get good collation features -- and
this is mostly solved.

I guess the biggest problem that remains is the inability to use LIKE
searches for index through utf8 ordered indexes.

merlin