Ravi Krishna <srkrishna@fastmail.com> writes:
> I am trying to create an index on function date_trunc('month',timestamp)
> PG is complaining that the function must be marked as IMMUTABLE.
The timestamptz version of it is not immutable, because its effects depend
on the timezone setting:
regression=# set timezone = 'America/New_York';
SET
regression=# select date_trunc('month', now());
date_trunc
------------------------
2018-12-01 00:00:00-05
(1 row)
regression=# set timezone = 'Europe/Paris';
SET
regression=# select date_trunc('month', now());
date_trunc
------------------------
2018-12-01 00:00:00+01
(1 row)
If you want immutability, you need to be working with timestamp-without-tz
or date input, so that timezone isn't a factor.
regards, tom lane
