On Sep 22, 2011, at 17:13, David Johnston wrote: > Is there some other way to create an index on only the "date" portion of the > field?
The cast from timestamptz to date is time zone dependent, which is why it's volatile and can't be used indexes. However, you can do this: test=# create table timestamps (ts timestamp with time zone primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "timestamps_pkey" for table "timestamps" CREATE TABLE test=# create index ts_date_idx on timestamps (cast(ts AS DATE)); ERROR: functions in index expression must be marked IMMUTABLE test=# create index ts_date_idx on timestamps (cast(ts at time zone 'UTC' AS DATE)); CREATE INDEX Just decide what time zone you want the index to be in. > Is it even necessary since any index ordered on timestamp is also, > by definition, order on date as well? Another option is to use some other operator rather than =, if you're selecting items, such as BETWEEN or >= and <, such as WHERE field >= date0 AND field < date0 + 1 This would use an index on the timestamp column. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
