[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
|
| Список | 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
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 по дате отправления: