|
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 |
