| From: | "Sven R(dot) Kunze" <srkunze(at)mail(dot)de> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | ERROR: functions in index expression must be marked IMMUTABLE |
| Date: | 2017-02-26 10:09:04 |
| Message-ID: | cffdbfb9-9173-f756-a54b-73b973c9b115@mail.de |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
expressionanyway and mark it as immutable.
So, what is the problem here?
Regards,
Sven
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Sven R. Kunze | 2017-02-26 11:26:03 | Querying JSON Lists |
| Previous Message | John R Pierce | 2017-02-26 02:01:01 | Re: Cavium ThunderX Processors used for PostgreSQL? |