Re: Creating index with UPPER

Michael Fork <mfork(at)toledolink(dot)com> writes:
> I am trying to create an index that is case insensitive (i.e. by making
> the index on UPPER(col) and then selecting using WHERE col =
> Upper('str')). However, the column is defined as a varchar, and I have
> been unable to get it working (expects type text)
> test=# CREATE INDEX idx_foo_bar ON foo(UPPER(bar));
> ERROR: DefineIndex: function 'upper(varchar)' does not exist

Yes --- 7.0.* will not accept binary-compatible functions for functional
indexes, it wants an exact match on the function's input datatype. This
is fixed for 7.1, but in the meanwhile you could either change the
table's type to text, or hack up a pg_proc entry for upper(varchar).
This should do the trick in 7.0.*:
create function upper(varchar) returns text as 'upper'
language 'internal' with (iscachable);

You'll want to get rid of this function definition when you migrate to
7.1, but I don't think this hack will have any bad side-effects as long
as you're on 7.0.

regards, tom lane