Re: [SQL] Functional Indexes

Marc Howard Zuckman <marc@fallon.classyad.com> writes:
> On Mon, 8 Feb 1999, Sascha Schumann wrote:
>> funweb=> create unique index userdat_idx2 on userdat (lower(username)
>> varchar_ops);
>> ERROR:  BuildFuncTupleDesc: function 'lower(varchar)' does not exist
>>
>> This error message looks very bogus to me.

> I don't think lower is defined for varchar arguments.  consider redefining
> username as type text and using text_ops.

I think Marc is right.  Someone was working on adding lower() to the
available ops for varchar for 6.5, but it's not there in 6.4.

You can get lower() to work on varchar source data in a simple
SELECT, but that's some sort of hack that involves the system
knowing that text and varchar have the same physical representation
so it's OK to use a function that takes text on a varchar column.
The type matching requirements for functional indexes are tighter.

Note to hackers: is there a good reason why indexes are more
restrictive?  Offhand it seems like the same physical-equivalence
trick could be applied.

            regards, tom lane