I am checking the possibilities of using jsonb fields and can’t create index on jsonb element if I cast it to timestamp with time zone:
drop table if exists test_json;
create table test_json (j jsonb);
insert into test_json select jsonb_build_object('id',1,'time', now()::timestamptz);
--OK
create index test_json_idx1 on test_json(((j->>'id')::integer));
--ERROR
create index test_json_idx2 on test_json(((j->>'time')::timestamptz));
The message is :
ERROR: FEHLER: Funktionen im Indexausdruck müssen als IMMUTABLE markiert sein
Cast from time to timestamptz is not immutable function. More - indexing time value casted to timestamp value is strange - are you sure, so you want to do this? Time miss date part - so this transformation every day returns different value.
Sorry, It was bad reply - column named "time" should not be "time" type.
Unfortunatelly it's hard task, because time zone can be dynamically changed, and conversions from text->timestamptz depends on.
So this expression cannot be part of functional index. There are some ugly solutions, but it's work only when default timezone is not changed.
For this purpose probably the best solution is aux column with this value stored like native timestamptz type and filled by trigger.
Mandatory Information for business emails according to German trade laws / Pflichtangaben für geschäftliche E-mails gemäß Handelsgesetzbuch:
Auconet GmbH - ein Unternehmen der Beta Systems Gruppe An den Treptowers 1 12435 Berlin Germany Phone: +49-(0)30-254 690-0 Fax: +49-(0))30-254 690-199 info@auconet-it.com www.auconet-it.com
