Thread: Cannot CREATE INDEX that contains a function

I am testing Postgresql-7.0.3

Please tell me what I am doing bad.

In psql...
testdb=# CREATE TABLE test (username varchar(50));
CREATE
testdb=# CREATE UNIQUE INDEX test_index ON test (lower(username));
ERROR: DefineIndex: function 'lower(varchar)' does not exist

The same happens using upper, initcap,...
Roberto Bertolusso

Roberto Bertolusso <rb@desinet-sa.com> writes:
> testdb=# CREATE TABLE test (username varchar(50));
> CREATE
> testdb=# CREATE UNIQUE INDEX test_index ON test (lower(username));
> ERROR: DefineIndex: function 'lower(varchar)' does not exist

Short answer in 7.0.* is to make the column be type text not varchar.
7.1 is more flexible about this ...
        regards, tom lane