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: | Raw Message | Whole Thread | 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? |