Re: Case Insensitive Queries

Can you please explain in little more detail? I am curious.

I haven't noticed any discussion about upper() being different from
lower() when it comes to such comparisons. As far as I know, upper()
and lower() only operate on ascii characters a-z. If you are using the
default locale, neither function should have any impact on characters
in the extended ascii range.

If upper() and lower() operate on characters in 8859-1 and other character
sets when the appropriate locale is set, then a difference in the behavior
of upper() and lower() would seem like a bug.

If you can shed some light on this, I would appreciate it.

Thanks,

Troy

>
> Mark writes:
> > Is it possible to execute a query using a where clause that allows case
> > insensitive comparison between a field and text.
>
> select * from account where upper(username) = upper('test')
>
> (Upper used because, as has been remarked on this list and in other places,
> folding from richer character sets is likely to get better matches this way).
>
> And yes, you can create an index on upper(fieldname).
>
> Dan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>