From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Raschkowski, Michael" <michael(dot)raschkowski(at)auconet-it(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Cannot create index on Jsonb timestamp field |
Date: | 2020-01-13 15:56:47 |
Message-ID: | CAFj8pRDthqpnScdTzeZNU+2APS4Fuh8-=7FMuJhgRJw_GodFHQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
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
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2020-01-13 16:26:41 | Re: Cannot create index on Jsonb timestamp field |
Previous Message | Raschkowski, Michael | 2020-01-13 14:41:33 | Cannot create index on Jsonb timestamp field |