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
