Re: Create index on the year of a date column

On Thu, 5 Jun 2003, Nick Barr wrote:

> CREATE SEQUENCE "sm_item_id_seq" start 1 increment 1 maxvalue
> 9223372036854775807 minvalue 1 cache 1;
> CREATE TABLE "sm_item" (
>     "item_id" int4 DEFAULT nextval('"sm_item_id_seq"'::text) NOT
> NULL,
>     "item_created_date_start" date,
>     CONSTRAINT "sm_item_pkey" PRIMARY KEY ("item_id")
> ) WITHOUT OIDS;
> ------------------------------------------------------------------------
> ----
>
> 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));
> ------------------------------------------------------------------------

In 7.4, I believe you'll be allowed to say
sm_item((extract(year from item_created_date_start)))

For now, you'd need to make an immutable function that does the extract
year from $1 and use that in the index and query, although I agree with
the other response that it might just be better to index the whole value
and use range queries instead.