Thread: 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
Attachment
Re: [pgsql-ru-general] Re: [pgsql-ru-general] индекс по полю JSON без функции
From
Sergey Konoplev
Date:
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