Re: Cannot create index on Jsonb timestamp field
Pavel Stehule <[email protected]> Mon, 13 Jan 2020 17:26:41 +0100
| Newsgroups | gmane.comp.db.postgresql.bugs |
|---|---|
| Message-ID | <CAFj8pRA_HNqhMYHnrtOEi8+1mdGnaWuUmPUi2o28R15Xxo6ncw@mail.gmail.com> |
--000000000000ecbb1e059c07f2e7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable po 13. 1. 2020 v 16:56 odes=C3=ADlatel Pavel Stehule <[email protected]= om> napsal: > > > 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 = 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=C3=BCssen als IMMUTABLE mar= kiert >> 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 wa= nt > to do this? Time miss date part - so this transformation every day return= s > 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=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 Han= delsgesetzbuch: >> >> 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 14= 1 >> 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 >> >> >> >> >> > --000000000000ecbb1e059c07f2e7 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:56 odes=C3= =ADlatel Pavel Stehule <<a href=3D"mailto:[email protected]">pavel= [email protected]</a>> napsal:<br></div><blockquote class=3D"gmail_quot= e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)= ;padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div cla= ss=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">po 13. 1. 2020 v= =C2=A016:48 odes=C3=ADlatel Raschkowski, Michael <<a href=3D"mailto:mich= [email protected]" target=3D"_blank">michael.raschkowski@aucon= et-it.com</a>> napsal:<br></div><blockquote class=3D"gmail_quote" style= =3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding= -left:1ex"><div lang=3D"DE"><div><p class=3D"MsoNormal"><span lang=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 crea= te index on jsonb element if I cast it to timestamp with time zone:<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">drop 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></span></p><p cla= ss=3D"MsoNormal"><span lang=3D"EN-US">insert into test_json select jsonb_bu= ild_object('id',1,'time', 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 index test_jso= n_idx1 on test_json(((j->>'id')::integer));<u></u><u></u></sp= an></p><p class=3D"MsoNormal"><span lang=3D"EN-US">--ERROR<u></u><u></u></s= pan></p><p class=3D"MsoNormal"><span lang=3D"EN-US">create index test_json_= idx2 on test_json(((j->>'time')::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 message is :<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 style=3D"font-size:9.5pt;= font-family:Consolas;color:rgb(34,34,34);background:white none repeat scrol= l 0% 0%">ERROR: FEHLER: Funktionen im Indexausdruck m=C3=BCssen als IMMUTAB= LE markiert sein </span></p></div></div></blockquote><div><br></div><div>Ca= st 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 dif= ferent value.</div></div></div></blockquote><div><br></div><div>Sorry, It w= as bad reply - column named "time" should not be "time"= type. <br></div><div><br></div><div>Unfortunatelly it's hard task, bec= ause time zone can be dynamically changed, and conversions from text->ti= mestamptz depends on.</div><div><br></div><div>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.</div><div><br></div><div>For th= is purpose probably the best solution is aux column with this value stored = like native timestamptz type and filled by trigger.</div><div><br></div><di= v>Regards</div><div><br></div><div>Pavel<br></div><div><br></div><div><br><= /div><div><br></div><div><br></div><blockquote class=3D"gmail_quote" style= =3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding= -left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div><br></div><div>= Pavel<br></div><div><br></div><div> <br></div><blockquote class=3D"gmail_qu= ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20= 4);padding-left:1ex"><div lang=3D"DE"><div><p class=3D"MsoNormal"><span sty= le=3D"font-size:9.5pt;font-family:Consolas;color:rgb(34,34,34);background:w= hite none repeat scroll 0% 0%"><u></u><u></u></span></p><p class=3D"MsoNorm= al"><span style=3D"font-size:9.5pt;font-family:Consolas;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: 4= 2P17<u></u><u></u></span></p><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p= ><table style=3D"width:100%;border-collapse:collapse" width=3D"100%" cellsp= acing=3D"0" cellpadding=3D"0" border=3D"0"><tbody><tr><td style=3D"padding:= 0cm"><p class=3D"MsoNormal" style=3D"margin-bottom:12pt;line-height:105%"><= span style=3D"font-size:12pt;line-height:105%">Viele 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 Systems Gruppe<br></b><span s= tyle=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:"Times New Roman",serif;color:b= lue"><br></span></u><u><span style=3D"font-size:12pt;line-height:105%;color= :blue">Email: <a href=3D"mailto:[email protected]" target= =3D"_blank"><span style=3D"color:blue">[email protected]</= span></a></span></u><span style=3D"font-size:12pt;line-height:105%;font-fam= ily:"Times New Roman",serif"><br><a href=3D"http://www.auconet-it= .com/" target=3D"_blank"><span style=3D"color:blue">www.auconet-it.com</spa= n></a><a name=3D"m_6408937943411213170_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"line-= height:105%"><span style=3D"font-size:12pt;line-height:105%;font-family:&qu= ot;Times New Roman",serif">=C2=A0<u></u><u></u></span></p><p class=3D"= MsoNormal" style=3D"line-height:105%"><span style=3D"font-size:12pt;line-he= ight:105%;font-family:"Times New Roman",serif">=C2=A0<u></u><u></= u></span></p><p class=3D"MsoNormal" style=3D"line-height:105%"><span style= =3D"font-size:7.5pt;line-height:105%">Mandatory Information for business em= ails according to German trade laws / Pflichtangaben f=C3=BCr gesch=C3=A4ft= liche 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 Berlin<= br>Germany<br>Phone: +49-(0)30-254 690-0<br>Fax: +49-(0))30-254 690-199<br>= <a href=3D"mailto:[email protected]" target=3D"_blank"><span style=3D"col= or:blue">[email protected]</span></a><br><a href=3D"http://www.auconet-it= .com/" target=3D"_blank"><span style=3D"color:blue">www.auconet-it.com</spa= n></a><br><br>Management / Gesch=C3=A4ftsf=C3=BChrer: Hartmut Bolten, Sebas= tian Zang<br>Legal form / Rechtsform: GmbH | Registered office / Sitz: Berl= in<br>Commercial register / Handelsregister: Amtsgericht Charlottenburg 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 1= 004 0000 0230 9391 00</span><span style=3D"font-size:12pt;line-height:105%;= font-family:"Times New Roman",serif"><u></u><u></u></span></p></t= d></tr></tbody></table><p class=3D"MsoNormal"><span><u></u>=C2=A0<u></u></s= pan></p><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p></div></div></blockq= uote></div></div> </blockquote></div></div> --000000000000ecbb1e059c07f2e7--