Thread: Creating index with UPPER

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)

Thanks

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

test=# CREATE TABLE foo (bar VARCHAR(5));
CREATE
test=# CREATE INDEX idx_foo_bar ON foo(UPPER(bar));
ERROR:  DefineIndex: function 'upper(varchar)' does not exist
test=# CREATE INDEX idx_foo_bar ON foo(UPPER(bar::text));
ERROR:  parser: parse error at or near "::"
test=# CREATE INDEX idx_foo_bar ON foo(UPPER(CAST(bar AS TEXT)));
ERROR:  parser: parse error at or near "cast"
test=# CREATE INDEX idx_foo_bar ON foo(UPPER(TEXT(bar)));
ERROR:  parser: parse error at or near "("

Michael Fork <mfork@toledolink.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