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

есть в табличке JSON-чик

там неопределенный набор полей в хеше поэтому JSON
но поле time в JSON есть почти всегда

хочу построить по нему индекс

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.

функцию выделенную делать не хочется.

как быть?
--

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

> Нельзя поле time вынести из JSON, если оно почти всегда есть?

можно, но не хочется этого делать ибо этот хешик как самостоятельная
сущность и она очень красиво в отдельном поле смотрится.
--

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

что, если использовать тип данных timestamp without timezone?

2013/9/24 Dmitry E. Oboukhov <unera(at)debian(dot)org>

>
> есть в табличке JSON-чик
>
> там неопределенный набор полей в хеше поэтому JSON
> но поле time в JSON есть почти всегда
>
> хочу построить по нему индекс
>
> 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.
>
> функцию выделенную делать не хочется.
>
> как быть?
> --
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlJCDJYACgkQq4wAz/jiZTerDwCgwQ6czkLG8RhabD5tM3WAMHoo
> yiYAnjFD5Wfz6h0g5nOI5EPCARvBQAHC
> =JLzA
> -----END PGP SIGNATURE-----
>
>

2013/9/24 Dmitry E. Oboukhov <unera(at)debian(dot)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(dot)ru(at)gmail(dot)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(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
`. `~’ GPGKey: 1024D / F8E26537 2006-11-21
`- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537

2013/9/25 Dmitry E. Oboukhov <unera(at)debian(dot)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(dot)ru(at)gmail(dot)com