pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedCannot create index on Jsonb timestamp field
3+ messages / 2 participants
[nested] [flat]
* Cannot create index on Jsonb timestamp field
@ 2020-01-13 14:41 Raschkowski, Michael <michael.raschkowski@auconet-it.com>
0 siblings, 1 reply; 3+ messages in thread
From: Raschkowski, Michael @ 2020-01-13 14:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
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: <mailto:michael.raschkowski@auconet-it.com>
michael.raschkowski@auconet-it.com
<http://www.auconet-it.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
<mailto:info@auconet-it.com> info@auconet-it.com
<http://www.auconet-it.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Cannot create index on Jsonb timestamp field
@ 2020-01-13 15:56 Pavel Stehule <pavel.stehule@gmail.com>
parent: Raschkowski, Michael <michael.raschkowski@auconet-it.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pavel Stehule @ 2020-01-13 15:56 UTC (permalink / raw)
To: Raschkowski, Michael <michael.raschkowski@auconet-it.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
po 13. 1. 2020 v 16:48 odesílatel Raschkowski, Michael <
michael.raschkowski@auconet-it.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.raschkowski@auconet-it.com
> <michael.raschkowski@auconet-it.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@auconet-it.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
>
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Cannot create index on Jsonb timestamp field
@ 2020-01-13 16:26 Pavel Stehule <pavel.stehule@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Pavel Stehule @ 2020-01-13 16:26 UTC (permalink / raw)
To: Raschkowski, Michael <michael.raschkowski@auconet-it.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
po 13. 1. 2020 v 16:56 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:
>
>
> po 13. 1. 2020 v 16:48 odesílatel Raschkowski, Michael <
> michael.raschkowski@auconet-it.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.
>
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üß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.raschkowski@auconet-it.com
>> <michael.raschkowski@auconet-it.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@auconet-it.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
>>
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-01-13 16:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-13 14:41 Cannot create index on Jsonb timestamp field Raschkowski, Michael <michael.raschkowski@auconet-it.com>
2020-01-13 15:56 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-01-13 16:26 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox