Re: [SQL] index on aggregate function

> > > I somewhere read that it is possible to create an index on aggregate functions
> > > in PostgreSQL. Which syntax do I have to use for it?
> > >
> > > test=> CREATE TABLE test ( name VARCHAR(32) );
> > > CREATE
> > > test=> CREATE INDEX test_idx ON test lower(name);
> > > ERROR: parser: parse error at or near "lower"
> > >
> > > gives me a parser error.
> >
> > Right syntax is
> > CREATE INDEX test_idx ON test (lower(name) text_ops);
> > I try it and index was generated without error
> > Rem
>
> Well, that exact statement gives me:
>
> ERROR: BuildFuncTupleDesc: function 'lower(varchar)' does not exist
>
> I also tried varchar_ops and char_ops instead of text_ops, but that does not
> resolve the problem. I'm using PostgreSQL 6.4.2 on Linux without problems so
> far.

I have holidays and now have to read so much mails, that I can't find if
there were any other suggestions about that. AFAIK there is no lower()
function for varchar type and this is a problem - solution is to use text
type or create function lower() for varchar type.
Rem

-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems(at)gdansk(dot)sprint(dot)pl * *
-----------------------------------------------------------------*****----------