Re: Cannot create index on Jsonb timestamp field

--000000000000ff7582059c078737
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

po 13. 1. 2020 v 16:48 odes=C3=ADlatel Raschkowski, Michael <
[email protected]> napsal:

> Hi,
>
>
>
> I am checking the possibilities of using jsonb fields and can=E2=80=99t c=
reate
> 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=C3=BCssen als IMMUTABLE mark=
iert
> 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=C3=BC=C3=9Fe / 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: [email protected]
> <[email protected]>*
> www.auconet-it.com
>
>
>
>
>
> Mandatory Information for business emails according to German trade laws =
/
> Pflichtangaben f=C3=BCr gesch=C3=A4ftliche E-mails gem=C3=A4=C3=9F Handel=
sgesetzbuch:
>
> 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
> [email protected]
> www.auconet-it.com
>
> Management / Gesch=C3=A4ftsf=C3=BChrer: 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 000=
0
> 0230 9391 00
>
>
>
>
>

--000000000000ff7582059c078737
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">po 13. 1. 2020 v=C2=A016:48 odes=C3=
=ADlatel Raschkowski, Michael &lt;<a href=3D"mailto:michael.raschkowski@auc=
onet-it.com">[email protected]</a>&gt; napsal:<br></div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex"><div lang=3D"DE"><div class=
=3D"gmail-m_4089402612664520721WordSection1"><p class=3D"MsoNormal"><span l=
ang=3D"EN-US">Hi,<u></u><u></u></span></p><p class=3D"MsoNormal"><span lang=
=3D"EN-US"><u></u>=C2=A0<u></u></span></p><p class=3D"MsoNormal"><span lang=
=3D"EN-US">I am checking the possibilities of using jsonb fields and can=E2=
=80=99t create index on jsonb element if I cast it to timestamp with time z=
one:<u></u><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"EN-US"><u=
></u>=C2=A0<u></u></span></p><p class=3D"MsoNormal"><span lang=3D"EN-US">dr=
op table if exists test_json;<u></u><u></u></span></p><p class=3D"MsoNormal=
"><span lang=3D"EN-US">create table test_json (j jsonb);<u></u><u></u></spa=
n></p><p class=3D"MsoNormal"><span lang=3D"EN-US">insert into test_json sel=
ect jsonb_build_object(&#39;id&#39;,1,&#39;time&#39;, now()::timestamptz);<=
u></u><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"EN-US">--OK<u>=
</u><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"EN-US">create in=
dex test_json_idx1 on test_json(((j-&gt;&gt;&#39;id&#39;)::integer));<u></u=
><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"EN-US">--ERROR<u></=
u><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"EN-US">create inde=
x test_json_idx2 on test_json(((j-&gt;&gt;&#39;time&#39;)::timestamptz));<u=
></u><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"EN-US"><u></u>=
=C2=A0<u></u></span></p><p class=3D"MsoNormal"><span lang=3D"EN-US">The mes=
sage is :<u></u><u></u></span></p><p class=3D"MsoNormal"><span lang=3D"EN-U=
S"><u></u>=C2=A0<u></u></span></p><p class=3D"MsoNormal"><span style=3D"fon=
t-size:9.5pt;font-family:Consolas;color:rgb(34,34,34);background:white none=
 repeat scroll 0% 0%">ERROR: FEHLER: Funktionen im Indexausdruck m=C3=BCsse=
n als IMMUTABLE markiert sein </span></p></div></div></blockquote><div><br>=
</div><div>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 da=
y returns different value.</div><div><br></div><div>Pavel<br></div><div><br=
></div><div> <br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv lang=3D"DE"><div class=3D"gmail-m_4089402612664520721WordSection1"><p cl=
ass=3D"MsoNormal"><span style=3D"font-size:9.5pt;font-family:Consolas;color=
:rgb(34,34,34);background:white none repeat scroll 0% 0%"><u></u><u></u></s=
pan></p><p class=3D"MsoNormal"><span style=3D"font-size:9.5pt;font-family:C=
onsolas;color:rgb(34,34,34);background:white none repeat scroll 0% 0%"><u><=
/u>=C2=A0<u></u></span></p><p class=3D"MsoNormal"><span style=3D"font-size:=
9.5pt;font-family:Consolas;color:rgb(34,34,34);background:white none repeat=
 scroll 0% 0%">SQL state: 42P17<u></u><u></u></span></p><p class=3D"MsoNorm=
al"><u></u>=C2=A0<u></u></p><table style=3D"width:100%;border-collapse:coll=
apse" width=3D"100%" cellspacing=3D"0" cellpadding=3D"0" border=3D"0"><tbod=
y><tr><td style=3D"padding:0cm"><p class=3D"MsoNormal" style=3D"margin-bott=
om:12pt;line-height:105%"><span style=3D"font-size:12pt;line-height:105%">V=
iele Gr=C3=BC=C3=9Fe / Kind regards,<br><br><b>Dr. Michael Raschkowski</b> =
<br>Software Architect<br><br><b>Auconet GmbH - ein Unternehmen der Beta Sy=
stems Gruppe<br></b><span style=3D"color:black">An den Treptowers 1, 12435 =
Berlin<br>Phone: +49 30 254 690-0<br>Fax: +49 30 254 690-199</span></span><=
u><span style=3D"font-size:12pt;line-height:105%;font-family:&quot;Times Ne=
w Roman&quot;,serif;color:blue"><br></span></u><u><span style=3D"font-size:=
12pt;line-height:105%;color:blue">Email: <a href=3D"mailto:michael.raschkow=
[email protected]" target=3D"_blank"><span style=3D"color:blue">michael.ra=
[email protected]</span></a></span></u><span style=3D"font-size:12pt=
;line-height:105%;font-family:&quot;Times New Roman&quot;,serif"><br><a hre=
f=3D"http://www.auconet-it.com/" target=3D"_blank"><span style=3D"color:blu=
e">www.auconet-it.com</span></a><a name=3D"m_4089402612664520721__GoBack"><=
/a><b><u></u><u></u></b></span></p></td></tr><tr style=3D"height:101.25pt">=
<td style=3D"padding:0cm;height:101.25pt"><p class=3D"MsoNormal" style=3D"l=
ine-height:105%"><span style=3D"font-size:12pt;line-height:105%;font-family=
:&quot;Times New Roman&quot;,serif">=C2=A0<u></u><u></u></span></p><p class=
=3D"MsoNormal" style=3D"line-height:105%"><span style=3D"font-size:12pt;lin=
e-height:105%;font-family:&quot;Times New Roman&quot;,serif">=C2=A0<u></u><=
u></u></span></p><p class=3D"MsoNormal" style=3D"line-height:105%"><span st=
yle=3D"font-size:7.5pt;line-height:105%">Mandatory Information for business=
 emails according to German trade laws / Pflichtangaben f=C3=BCr gesch=C3=
=A4ftliche E-mails gem=C3=A4=C3=9F Handelsgesetzbuch:<br><br>Auconet GmbH -=
 ein Unternehmen der Beta Systems Gruppe<br>An den Treptowers 1<br>12435 Be=
rlin<br>Germany<br>Phone: +49-(0)30-254 690-0<br>Fax: +49-(0))30-254 690-19=
9<br><a href=3D"mailto:[email protected]" target=3D"_blank"><span style=
=3D"color:blue">[email protected]</span></a><br><a href=3D"http://www.auc=
onet-it.com/" target=3D"_blank"><span style=3D"color:blue">www.auconet-it.c=
om</span></a><br><br>Management / Gesch=C3=A4ftsf=C3=BChrer: Hartmut Bolten=
, Sebastian Zang<br>Legal form / Rechtsform: GmbH | Registered office / Sit=
z: Berlin<br>Commercial register / Handelsregister: Amtsgericht Charlottenb=
urg HRB 141 887 B<br>VAT-ID / Ust-ID-Nr.: DE316422231 | Tax No. / St.-Nr.: =
30/036/75431<br>Bank / Bankverbindung: Commerzbank AG, BIC: COBADEFF, IBAN:=
 DE25 1004 0000 0230 9391 00</span><span style=3D"font-size:12pt;line-heigh=
t:105%;font-family:&quot;Times New Roman&quot;,serif"><u></u><u></u></span>=
</p></td></tr></tbody></table><p class=3D"MsoNormal"><span><u></u>=C2=A0<u>=
</u></span></p><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p></div></div><=
/blockquote></div></div>

--000000000000ff7582059c078737--