Обсуждение: using possibly null timestamptz columns
Given a table with a pair of timestamptz columns (lets call them s and e) which are typically null, is there a better way to write this where clause snippet: where ( s is null or s <= now() ) and ( e is null or e >= now() ) In particular I hope to ensure efficient indexes. -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6
James Cloos <cloos@jhcloos.com> writes:
> Given a table with a pair of timestamptz columns (lets call them s and e)
> which are typically null, is there a better way to write this where clause
> snippet:
> where ( s is null or s <= now() ) and ( e is null or e >= now() )
You could try constructing a GIST or SPGIST index on the ranges
tstzrange(s, e), where you'd have to do something to convert null
endpoints to infinities, and then probing with WHERE rangeexpr @> now().
I'm not really sure how well this would perform, but certainly you're
dead in the water as far as doing anything useful with regular btree
indexes.
regards, tom lane
I wrote:
> You could try constructing a GIST or SPGIST index on the ranges
> tstzrange(s, e), where you'd have to do something to convert null
> endpoints to infinities, and then probing with WHERE rangeexpr @> now().
Or actually, it looks like tstzrange() already does the right thing:
regression=# select tstzrange(now(), null); tstzrange
-----------------------------------["2016-09-29 09:12:14.79429-04",)
(1 row)
regression=# select tstzrange(null, now()); tstzrange
------------------------------------(,"2016-09-29 09:12:33.632327-04")
(1 row)
So this just reduces to WHERE tstzrange(s, e) @> now().
regards, tom lane
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> So this just reduces to WHERE tstzrange(s, e) @> now(). Thank you! I forgot about the range types. I may even switch to a single range column instead of the separate start and end columns. -JimC -- James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6