Re: Cannot create index on Jsonb timestamp field

po 13. 1. 2020 v 16:56 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
> po 13. 1. 2020 v 16:48 odesílatel Raschkowski, Michael <
> michael(dot)raschkowski(at)auconet-it(dot)com> napsal:
>
>> Hi,
>>
>>
>>
>> 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.

Regards

Pavel

> Pavel
>
>
>
>>
>> SQL state: 42P17
>>
>>
>>
>> Viele Grüße / Kind regards,
>>
>> *Dr. Michael Raschkowski*
>> Software Architect
>>
>>
>> *Auconet GmbH - ein Unternehmen der Beta Systems Gruppe*An den
>> Treptowers 1, 12435 Berlin
>> Phone: +49 30 254 690-0
>> Fax: +49 30 254 690-199
>> *Email: michael(dot)raschkowski(at)auconet-it(dot)com
>> <michael(dot)raschkowski(at)auconet-it(dot)com>*
>> www.auconet-it.com
>>
>>
>>
>>
>>
>> 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(at)auconet-it(dot)com
>> www.auconet-it.com
>>
>> Management / Geschäftsführer: Hartmut Bolten, Sebastian Zang
>> Legal form / Rechtsform: GmbH | Registered office / Sitz: Berlin
>> Commercial register / Handelsregister: Amtsgericht Charlottenburg HRB 141
>> 887 B
>> VAT-ID / Ust-ID-Nr.: DE316422231 | Tax No. / St.-Nr.: 30/036/75431
>> Bank / Bankverbindung: Commerzbank AG, BIC: COBADEFF, IBAN: DE25 1004
>> 0000 0230 9391 00
>>
>>
>>
>>
>>
>