Re: Index on Date_Trunc

"Nathan Barnett" <nbarnett@cellularphones.com> writes:
> I am trying to create an index on the date_trunc('day', columna) in a table.
> When I try to create this index, I get a parse error near the single quote.
> Is there a workaround to create this index?  I am using v7.0.3 on FreeBSD.

Functional indexes can only be on a simple function of one or more
simple column names, ie, "f(a,b,c)".  There has been talk of relaxing
this syntactic restriction, but it doesn't seem to be high on anyone's
priority list.  The reason is that you can work around it by creating
an intermediate user-defined function that computes any expression you
want based on the given column values.  For instance, in this case
you'd make a function dayof(date) and then build the index on that.

In 7.0.* I believe that you need to write the function in a PL language
(plpgsql would be the most convenient choice), or if you are talking
about a heavily used table, you might want to write it in C for speed.

7.1 will allow functional indexes on SQL-language functions too, though
the speed might not be what you'd like...

            regards, tom lane