I figured out the issue, it was as simple as some developers used the default in Java's Hibernate which created the timestamp columns without time zone. Anyway I guess this is the correct approach that also take summer time into consideration? And using the immutable function wrapper is wrong? On Mon, Jun 12, 2017 at 2:25 PM, Olav Gjerde <olav@xxxxxxxxxxxxx> wrote: > I have a table that I try create an functional index on like this: > > CREATE INDEX my_index_name > ON opening_hours ( > opening_hours_type, > EXTRACT(YEAR FROM date), > EXTRACT(MONTH FROM date) > ) > > But I get the following error: functions in index expression must be > marked IMMUTABLE > > > But if I change it to: > > CREATE INDEX my_index_name > ON opening_hours ( > opening_hours_type, > EXTRACT(YEAR FROM date AT TIME ZONE 'UTC'), > EXTRACT(MONTH FROM date AT TIME ZONE 'UTC') > ) > The index will be created. > > On other systems it could be the oppsite, that it only works without > the additional AT TIME ZONE > > On all systems, the default Timezone in postgresql.conf is set to UTC > and show timezone; return UTC. Additionally show lc_time; returns > nb_NO.UTF-8 > > What kind of system settings could cause this behaviour? We run > Ubuntu Linux 16.04 and Postgresql 9.6 > > Another question is, should I just create immutable functions wrappers > for this instead? > > > -- > Kind Regards / Med Vennlig Hilsen > > Olav Grønås Gjerde -- Kind Regards / Med Vennlig Hilsen Olav Grønås Gjerde BackupBay Gjerde Asalvegen 19 4051 SOLA Norway Phone: +47 918 000 59 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
