[GENERAL] Timezone locale consistency for functional indexes

Поиск
Список
Период
Сортировка
От Olav Gjerde
Тема [GENERAL] Timezone locale consistency for functional indexes
Дата
Msg-id CAJ7kQyHeg9ta8wwBW1dJoubUHRh+ZnJeMMNOWNyH4Jx++oWbEA@mail.gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] Timezone locale consistency for functional indexes
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: pinker
Дата:
Сообщение: Re: [GENERAL] Huge Pages - setting the right value
Следующее
От: Achilleas Mantzios
Дата:
Сообщение: Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toastvalue 76753264 in pg_toast_10920100