Обсуждение: Bug #730: cannot create functional index

Vadim Grepan (kezal@mail.ru) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
cannot create functional index

Long Description
I've tried to create functional index like
that:
=== cut ===
db=> CREATE INDEX NDX_FNC_TBL_TRAFBYTES__DATE ON tbl_trafbytes (date (date_trunc ('day', trafbytes_date)));
ERROR:  parser: parse error at or near "("
=== cut ===

Column trafbytes_date define as
=== cut ===
 trafbytes_date    | timestamp without time zone | not null
=== cut ===

Does it means that PostgreSQL funcional indexes support only simplest fucntions like lower ()?


Sample Code


No file was uploaded with this report

On Sun, 4 Aug 2002 pgsql-bugs@postgresql.org wrote:

> Vadim Grepan (kezal@mail.ru) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> cannot create functional index
>
> Long Description
> I've tried to create functional index like
> that:
> === cut ===
> db=> CREATE INDEX NDX_FNC_TBL_TRAFBYTES__DATE ON tbl_trafbytes (date (date_trunc ('day', trafbytes_date)));
> ERROR:  parser: parse error at or near "("
> === cut ===
>
> Column trafbytes_date define as
> === cut ===
>  trafbytes_date    | timestamp without time zone | not null
> === cut ===
>
> Does it means that PostgreSQL funcional indexes support only simplest fucntions like lower ()?

Functional indexes must take as arguments one or more columns.
You'll need a function that wraps what you're attempting to do
and make the index (and where clauses) with that.

TODO item is:

* Allow CREATE INDEX zman_index ON test (date_trunc( 'day', zman) datetime_ops)
  fails index can't store constant parameters

---------------------------------------------------------------------------

Stephan Szabo wrote:
> On Sun, 4 Aug 2002 pgsql-bugs@postgresql.org wrote:
>
> > Vadim Grepan (kezal@mail.ru) reports a bug with a severity of 3
> > The lower the number the more severe it is.
> >
> > Short Description
> > cannot create functional index
> >
> > Long Description
> > I've tried to create functional index like
> > that:
> > === cut ===
> > db=> CREATE INDEX NDX_FNC_TBL_TRAFBYTES__DATE ON tbl_trafbytes (date (date_trunc ('day', trafbytes_date)));
> > ERROR:  parser: parse error at or near "("
> > === cut ===
> >
> > Column trafbytes_date define as
> > === cut ===
> >  trafbytes_date    | timestamp without time zone | not null
> > === cut ===
> >
> > Does it means that PostgreSQL funcional indexes support only simplest fucntions like lower ()?
>
> Functional indexes must take as arguments one or more columns.
> You'll need a function that wraps what you're attempting to do
> and make the index (and where clauses) with that.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026