On 9 Jan 2007 13:44:32 -0800, af300wsm@gmail.com <af300wsm@gmail.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.phpIf 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
