Bug #730: cannot create functional index

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