[GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

Поиск
Список
Период
Сортировка
От Sven R. Kunze
Тема [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE
Дата
Msg-id cffdbfb9-9173-f756-a54b-73b973c9b115@mail.de
обсуждение исходный текст
Ответы Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE  (Geoff Winkless <pgsqladmin@geoff.dj>)
Список pgsql-general
Дерево обсуждения
Hello everybody,

I'd like to implement a btree date index from json input data.

>>># \d docs
                         Table "public.docs"
 Column |  Type   |                     Modifiers                    
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('docs_id_seq'::regclass)
 meta   | jsonb   |
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id)

So, I did:

>>># create index docs_birthdate_idx ON docs using btree (((meta->>'birthdate')::date));
ERROR:  functions in index expression must be marked IMMUTABLE

Searching the Internet for a solution, I tried several variants of this:

>>># create index docs_birthdate_idx ON docs using btree ((to_timestamp(meta->>'birthdate', 'YYYY-MM-DD') at time zone 'UTC'));
ERROR:  functions in index expression must be marked IMMUTABLE

Years ago, I circumvented it by creating an immutable function. This, though, just hides the errors since I would use the mutable expression anyway and mark it as immutable.


So, what is the problem here?


Regards,
Sven

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

Предыдущее
От: John R Pierce
Дата:
Сообщение: Re: [GENERAL] Cavium ThunderX Processors used for PostgreSQL?
Следующее
От: "Sven R. Kunze"
Дата:
Сообщение: [GENERAL] Querying JSON Lists