Re: Why extract( ... from timestamp ) is not immutable?

On Wed, Jan 25, 2012 at 08:10:19AM -0800, Adrian Klaver wrote:
> On Wednesday, January 25, 2012 7:48:34 am hubert depesz lubaczewski wrote:
> > On Wed, Jan 25, 2012 at 07:44:14AM -0800, Adrian Klaver wrote:
> > > > I thought that this is what I will achieve with extract(epoch from
> > > > now() at time zone 'UTC') but clearly it doesn't work.
> > > > So what options do I have?
> > >
> > > Isn't extract(epoch from now()) getting what you want?
> >
> > you can't make index on it.
>
> I am afraid I am not following. So you can make an index on?:
>
> extract(epoch from now() at time zone 'UTC')

yes, I can:
$ create table z (i timestamptz);
CREATE TABLE

$ create index q on z (extract(epoch from i));
ERROR:  functions in index expression must be marked IMMUTABLE

$ create index q on z (extract(epoch from i at time zone 'UTC'));
CREATE INDEX

which - given the fact that extract(epoch from timestamp) is not
immutable, shouldn't be possible.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/