Обсуждение: Re: [pgsql-ru-general] индекс по полю JSON без функции

2013/9/24 Dmitry E. Oboukhov <unera@debian.org>:
> хочу построить по нему индекс
>
> CREATE INDEX ON "table" (("field"->>'time'))
>     WHERE ("field"->>'time') IS NOT NULL;
>
> но поскольку в SQL запросах хочется оперировать полем time именно как
> TIMESTAMP (а там CHECKER еще накинут на то что проверяет что time
> это либо null либо timestamp).
> то соответственно хочется индекс строить по типу timestamp (иначе
> после приведения этот индекс не используется)
>
> CREATE INDEX ON "table" ((("field"->>'time')::timestamp(0)))
>     WHERE ("field"->>'time') IS NOT NULL;
>
> ругается что должно быть immutable.

immutable cast из text в timestamp(tz) в postgres нет, т.к. результат
зависит от внешних факторов, таких как time zone сервера (если без tz)
и DateStyle. Можно написать функцию, например,
full_iso8601_to_timestamp(text), где будет проверка на то что текст в
полном ISO 8601 формате (со всем чем можно и таймзоной), тогда это
будет действительно immutable, и это смело можно будет применять в
индексах. Либо в соответствии с какой-то другой договорённостью,
главное чтобы небыло неоднозначностей.

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com

>> хочу построить по нему индекс
>>
>> CREATE INDEX ON "table" (("field"->>'time'))
>>     WHERE ("field"->>'time') IS NOT NULL;
>>
>> но поскольку в SQL запросах хочется оперировать полем time именно как
>> TIMESTAMP (а там CHECKER еще накинут на то что проверяет что time
>> это либо null либо timestamp).
>> то соответственно хочется индекс строить по типу timestamp (иначе
>> после приведения этот индекс не используется)
>>
>> CREATE INDEX ON "table" ((("field"->>'time')::timestamp(0)))
>>     WHERE ("field"->>'time') IS NOT NULL;
>>
>> ругается что должно быть immutable.

> immutable cast из text в timestamp(tz) в postgres нет, т.к. результат
> зависит от внешних факторов, таких как time zone сервера (если без tz)
> и DateStyle. Можно написать функцию, например,
> full_iso8601_to_timestamp(text), где будет проверка на то что текст в
> полном ISO 8601 формате (со всем чем можно и таймзоной), тогда это
> будет действительно immutable, и это смело можно будет применять в
> индексах. Либо в соответствии с какой-то другой договорённостью,
> главное чтобы небыло неоднозначностей.

у меня в тех JSON время хранится с зоной

POSIX::strftime('%F %T %z', localtime)

Постгрис зону тут отлично распознает

я просто функцию не хотел городить выделенную.
функции вещь - хреново документируемая.
--

. ''`.                               Dmitry E. Oboukhov
: :’  :   email: unera@debian.org jabber://UNera@uvw.ru
`. `~’              GPGKey: 1024D / F8E26537 2006-11-21
  `- 1B23 D4F8 8EC0 D902 0555  E438 AB8C 00CF F8E2 6537

2013/9/25 Dmitry E. Oboukhov <unera@debian.org>:
>>> CREATE INDEX ON "table" ((("field"->>'time')::timestamp(0)))
>>>     WHERE ("field"->>'time') IS NOT NULL;
>>>
>>> ругается что должно быть immutable.
>
>> immutable cast из text в timestamp(tz) в postgres нет, т.к. результат
>> зависит от внешних факторов, таких как time zone сервера (если без tz)
>> и DateStyle. Можно написать функцию, например,
>> full_iso8601_to_timestamp(text), где будет проверка на то что текст в
>> полном ISO 8601 формате (со всем чем можно и таймзоной), тогда это
>> будет действительно immutable, и это смело можно будет применять в
>> индексах. Либо в соответствии с какой-то другой договорённостью,
>> главное чтобы небыло неоднозначностей.
>
> у меня в тех JSON время хранится с зоной
> POSIX::strftime('%F %T %z', localtime)
> Постгрис зону тут отлично распознает
> я просто функцию не хотел городить выделенную.

К сожалению этого не избежать.

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com