Thread: 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