Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ir1wj-00085b-RY for pgsql-bugs@arkaria.postgresql.org; Mon, 13 Jan 2020 15:48:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ir1wi-0002uo-C3 for pgsql-bugs@arkaria.postgresql.org; Mon, 13 Jan 2020 15:48:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ir0uU-0006BE-Ri for pgsql-bugs@lists.postgresql.org; Mon, 13 Jan 2020 14:41:43 +0000 Received: from gate.auconet-it.com ([195.243.9.3]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ir0uR-0007Cl-C1 for pgsql-bugs@lists.postgresql.org; Mon, 13 Jan 2020 14:41:41 +0000 Received: from gate.auconet-it.com (localhost.localdomain [127.0.0.1]) by localhost (Email Security Appliance) with SMTP id 019A257B93_E1C819FB for ; Mon, 13 Jan 2020 14:41:35 +0000 (GMT) Received: from mail.auconet-it.com (mail.auconet.com [10.10.11.5]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "mailcow.auconet.com", Issuer "Auconet Sub Alpha" (not verified)) by gate.auconet-it.com (Sophos Email Appliance) with ESMTPS id 79BF25513A_E1C819EF for ; Mon, 13 Jan 2020 14:41:34 +0000 (GMT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by localhost (Mailerdaemon) with ESMTPSA id BA87A374E7 for ; Mon, 13 Jan 2020 15:41:33 +0100 (CET) From: "Raschkowski, Michael" To: Subject: Cannot create index on Jsonb timestamp field Date: Mon, 13 Jan 2020 15:41:33 +0100 Message-ID: <002401d5ca1f$8d4d2da0$a7e788e0$@auconet-it.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0025_01D5CA27.EF134350" Thread-Index: AdXKHgcB45Eu+fJgS/+9qCWhpbr3BA== Content-Language: de X-SASI-RCODE: 200 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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: michael.raschkowski@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 info@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

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=FCssen als IMMUTABLE = markiert sein

 

SQL state: 42P17

 

Viele Gr=FC=DFe / 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.raschkowski@auconet-it.com=
www.auconet-it.com

 

 

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
info@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

 

 

------=_NextPart_000_0025_01D5CA27.EF134350--