Re: How does one perform a case-insenstive query on test or char fields

On 9 Jan 2007 13:44:32 -0800, af300wsm(at)gmail(dot)com <af300wsm(at)gmail(dot)com> wrote:
>
> SELECT * FROM table WHERE thisfield = 'some text';
>
> How would I rewrite this query to search through the table looking at
> the text in the column "thisfield" for the string "some text" but have
> it perform a case insensitive search?
>

I wouldn't consider this a "search", but rather a lookup. You can find some
tips on how to do this in the link below.

http://archives.postgresql.org/pgsql-php/2003-05/msg00045.php

If you need "thisfield" to be indexed, then I would suggest creating an
index on the expression "LOWER(thisfield)", the below text is directly from
the user guide:

For example, a common way to do case-insensitive comparisons is to use the
lower function:

SELECT * FROM test1 WHERE lower(col1) = 'value';

This query can use an index, if one has been defined on the result of the
lower(col1) operation:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

You can read more about expression indexes here:

http://www.postgresql.org/docs/8.2/static/indexes-expressional.html