Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by s_kilk on Thursday July 21, 2016 @08:05AM
(#12136249)
Re datetime filters:
If the datetimes are ISO formatted strings, would regex matching not work for basic filtering?
where json_field.datetime similar to '2016-07-.*'
Of course, it'll probably be slow-tastic, and less convenient than native datetime manipulation, but hey, maybe it's enough to get unblocked?
Re:Unstructured Datatypes in Postgres – Hstore vs. JSON vs. ...
by garyclarke27 on Thursday July 21, 2016 @05:02PM
(#12140045)
You can use an expression index, which casts to timestamptz, would then be very fast to query and filter on such.
Right now that application stores that in a rather messy way. Exposing this as a table field would mean that configuration changes (New field/a different field) causes a structural change (alter table). Or you have to go down the 'Date1, Date2, Date3, Number1, Number2' ... path.
In short: I see no way to expose these pieces of information in fields without ugly hacks, hence my interest in HStore/JSON(B) - but datetime based filters would be quite nice to have..