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