ERROR: functions in index expression must be marked IMMUTABLE

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

Responses

Browse pgsql-general by date

  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?