Cannot create index on Jsonb timestamp field
"Raschkowski, Michael" <[email protected]> Mon, 13 Jan 2020 15:41:33 +0100
| Newsgroups | gmane.comp.db.postgresql.bugs |
|---|---|
| Message-ID | <[email protected]> |
This is a multipart message in MIME format.
------=_NextPart_000_0025_01D5CA27.EF134350
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
=20
I am checking the possibilities of using jsonb fields and can=92t create =
index
on jsonb element if I cast it to timestamp with time zone:
=20
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));
=20
The message is :
=20
ERROR: FEHLER: Funktionen im Indexausdruck m=FCssen als IMMUTABLE =
markiert
sein=20
=20
SQL state: 42P17
=20
Viele Gr=FC=DFe / Kind regards,
Dr. Michael Raschkowski=20
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: <mailto:[email protected]>
[email protected]
<http://www.auconet-it.com/> www.auconet-it.com
=20
=20
Mandatory Information for business emails according to German trade laws =
/
Pflichtangaben f=FCr gesch=E4ftliche E-mails gem=E4=DF =
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
<mailto:[email protected]> [email protected]
<http://www.auconet-it.com/> www.auconet-it.com
Management / Gesch=E4ftsf=FChrer: 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
=20
=20
------=_NextPart_000_0025_01D5CA27.EF134350
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta =
http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1"><meta name=3DGenerator content=3D"Microsoft Word =
15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DDE =
link=3D"#0563C1" vlink=3D"#954F72"><div class=3DWordSection1><p =
class=3DMsoNormal><span lang=3DEN-US>Hi,<o:p></o:p></span></p><p =
class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p><p =
class=3DMsoNormal><span lang=3DEN-US>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:<o:p></o:p></span></p><p =
class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p><p =
class=3DMsoNormal><span lang=3DEN-US>drop table if exists =
test_json;<o:p></o:p></span></p><p class=3DMsoNormal><span =
lang=3DEN-US>create table test_json (j jsonb);<o:p></o:p></span></p><p =
class=3DMsoNormal><span lang=3DEN-US>insert into test_json select =
jsonb_build_object('id',1,'time', =
now()::timestamptz);<o:p></o:p></span></p><p class=3DMsoNormal><span =
lang=3DEN-US>--OK<o:p></o:p></span></p><p class=3DMsoNormal><span =
lang=3DEN-US>create index test_json_idx1 on =
test_json(((j->>'id')::integer));<o:p></o:p></span></p><p =
class=3DMsoNormal><span lang=3DEN-US>--ERROR<o:p></o:p></span></p><p =
class=3DMsoNormal><span lang=3DEN-US>create index test_json_idx2 on =
test_json(((j->>'time')::timestamptz));<o:p></o:p></span></p><p =
class=3DMsoNormal><span lang=3DEN-US><o:p> </o:p></span></p><p =
class=3DMsoNormal><span lang=3DEN-US>The message is =
:<o:p></o:p></span></p><p class=3DMsoNormal><span =
lang=3DEN-US><o:p> </o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:9.5pt;font-family:Consolas;color:#222222;background:wh=
ite'>ERROR: FEHLER: Funktionen im Indexausdruck m=FCssen als IMMUTABLE =
markiert sein <o:p></o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:9.5pt;font-family:Consolas;color:#222222;background:wh=
ite'><o:p> </o:p></span></p><p class=3DMsoNormal><span =
style=3D'font-size:9.5pt;font-family:Consolas;color:#222222;background:wh=
ite'>SQL state: 42P17<o:p></o:p></span></p><p =
class=3DMsoNormal><o:p> </o:p></p><table class=3DMsoNormalTable =
border=3D0 cellspacing=3D0 cellpadding=3D0 width=3D"100%" =
style=3D'width:100.0%;border-collapse:collapse'><tr><td =
style=3D'padding:0cm 0cm 0cm 0cm'><p class=3DMsoNormal =
style=3D'margin-bottom:12.0pt;line-height:105%'><span =
style=3D'font-size:12.0pt;line-height:105%'>Viele Gr=FC=DFe / 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 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:12.0pt;line-height:105%;font-family:"Times New =
Roman",serif;color:blue'><br></span></u><u><span =
style=3D'font-size:12.0pt;line-height:105%;color:blue'>Email: <a =
href=3D"mailto:[email protected]"><span =
style=3D'color:blue'>[email protected]</span></a></span>=
</u><span style=3D'font-size:12.0pt;line-height:105%;font-family:"Times =
New Roman",serif'><br><a href=3D"http://www.auconet-it.com/"><span =
style=3D'color:blue'>www.auconet-it.com</span></a><a =
name=3D"_GoBack"></a><b><o:p></o:p></b></span></p></td></tr><tr =
style=3D'height:101.25pt'><td style=3D'padding:0cm 0cm 0cm =
0cm;height:101.25pt'><p class=3DMsoNormal =
style=3D'line-height:105%'><span =
style=3D'font-size:12.0pt;line-height:105%;font-family:"Times New =
Roman",serif'> <o:p></o:p></span></p><p class=3DMsoNormal =
style=3D'line-height:105%'><span =
style=3D'font-size:12.0pt;line-height:105%;font-family:"Times New =
Roman",serif'> <o:p></o:p></span></p><p class=3DMsoNormal =
style=3D'line-height:105%'><span =
style=3D'font-size:7.5pt;line-height:105%'>Mandatory Information for =
business emails according to German trade laws / Pflichtangaben f=FCr =
gesch=E4ftliche E-mails gem=E4=DF 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]"><span =
style=3D'color:blue'>[email protected]</span></a><br><a =
href=3D"http://www.auconet-it.com/"><span =
style=3D'color:blue'>www.auconet-it.com</span></a><br><br>Management / =
Gesch=E4ftsf=FChrer: Hartmut Bolten, Sebastian Zang<br>Legal form / =
Rechtsform: GmbH | Registered office / Sitz: Berlin<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 1004 0000 0230 9391 00</span><span =
style=3D'font-size:12.0pt;line-height:105%;font-family:"Times New =
Roman",serif'><o:p></o:p></span></p></td></tr></table><p =
class=3DMsoNormal><span =
style=3D'mso-fareast-language:DE'><o:p> </o:p></span></p><p =
class=3DMsoNormal><o:p> </o:p></p></div></body></html>
------=_NextPart_000_0025_01D5CA27.EF134350--