Being lazy, I've created a set of case incensitive text comparison
operators: =*, <*, >*, and !=*; the function for each just does an
UPPER() on both arguments and then uses the corresponding builtin
operator.
What would make these REALLY useful, is if when running something like:
SELECT * FROM foo WHERE bar =* 'baz';
postgres would know to use an index defined as:
CREATE INDEX idx_foo_bar ON foo (UPPER(bar));
Currently, the explain I get for the above situation is:
Seq Scan on foo (cost=0.00..8696.81 rows=1324 width=34) Filter: (upper(upper((bar)::text)) = 'BAZ'::text)
I am vaguely aware of the concept of operator classes, but from what I
understand, the op class has to be specified at index creation time;
seeing how this is just to save a little typing, I'd rather not have to
rely on these ops and opclasses being defined for the rest of the
database to work. Also I need to still be able to do SELECT * FROM foo
WHERE UPPER(bar) = UPPER('baz') and have it use the same index.
So, is there any way to make these operators use an index defined as
above?
Thanks,
Dmitri
