>> create a function lower index and instead of calling ilike call ~
>> lower('123')
>
> To clarify a little:
>
> CREATE INDEX table_a_lower_field_1_idx on table_a ((lower(field_1)));
> CREATE INDEX table_a_lower_field_2_idx on table_a ((lower(field_2)));
> SELECT
> *
> FROM
> table_a
> WHERE
> id != 10001
> AND
> (
> ( lower(field_1) = '123' )
> OR
> ( lower(field_2) = 'abc' )
> )
To put my own two cents in, I always try to make sure I use lower()
in the query on everything I'm comparing, as Josh originally
suggested, so I would do this:
lower(field_2) = lower('abc')
This ensures that both sides of the comparison are being downcased
the same way - otherwise there might be a discrepancy due to
collation differences, etc., between the client and the server sides.
This seems silly in this example, but I think it's a good habit.
- John Burger
MITRE
