Re: CREATE INDEX test_idx ON test (UPPER(varchar_field)) doesn't work...

On Wed, 9 Aug 2000, Philip Hallstrom wrote:

> devloki=> SELECT UPPER(field) FROM test;
> upper
> ------------
> TEST STRIN
> (1 row)
>
> devloki=> CREATE INDEX test_idx ON test (field);
> CREATE
> devloki=> CREATE INDEX test_upper_idx ON test (UPPER(field));
> ERROR: DefineIndex: function 'upper(varchar)' does not exist
> --------------------------------------------------------------------------
>
> Is there any other information I can provide? Should I send this on to
> -bugs?

I think the reason for this is that the function is
upper(text) returns text. The select is willing to
do the type conversion for you but the index creation
is not.

I'm not 100% sure it's a good idea, but IIRC text and
varchar are binary compatible. You probably could
get away with adding an entry in pg_proc for
upper(varchar) returns varchar using the same function
by adding a new row with only the prorettype and proargtypes
changed.