Re: Possible bug in CREATE INDEX? Was: Re: Create index

On Fri, 6 Jun 2003, Mattias Kregert wrote:

> > I am trying to create an index on the year of a date field, to speed up
> > some queries. Table structure is as follows
> [snip]
> > ------------------------------------------------------------------------
> > And I have tried the following to create the actual index
> > ------------------------------------------------------------------------
> > create index sm_item_cdates_idx ON sm_item (extract(year from
> > item_created_date_start));
> > ------------------------------------------------------------------------
> > The response I get from psql is
> > ------------------------------------------------------------------------
> > sm_live=# create index sm_item_cdates_idx ON sm_item (extract(year from
> > item_created_date_start));
> > ERROR:  parser: parse error at or near "(" at character 52
> > ------------------------------------------------------------------------
>
> I think this is a bug. Some functions/builtins can not be used in a

It's not, just a limitation that hadn't been taken out yet (until 7.4
basically).  The syntax for 7.3 says something to the effect
of func_name(column [, ...]) so all the arguments must be columns in the
table.  This means that date_part and substring and other functions which
need constants to be useful or things that aren't strictly speaking
functions really can't be used without wrapping.

> functional index. date_part() does not work either...

Sure it does, if you've got another column that has the timestamp unit
string in it, not that that's really useful as a meaningful index. ;)