agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedusing possibly null timestamptz columns
4+ messages / 2 participants
[nested] [flat]
* using possibly null timestamptz columns
@ 2016-09-29 12:35 James Cloos <cloos@jhcloos.com>
2016-09-29 13:05 ` Re: using possibly null timestamptz columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: James Cloos @ 2016-09-29 12:35 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: using possibly null timestamptz columns
2016-09-29 12:35 using possibly null timestamptz columns James Cloos <cloos@jhcloos.com>
@ 2016-09-29 13:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-09-29 13:15 ` Re: using possibly null timestamptz columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2016-09-29 13:05 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: using possibly null timestamptz columns
2016-09-29 12:35 using possibly null timestamptz columns James Cloos <cloos@jhcloos.com>
2016-09-29 13:05 ` Re: using possibly null timestamptz columns Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-09-29 13:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-09-29 15:48 ` Re: using possibly null timestamptz columns James Cloos <cloos@jhcloos.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2016-09-29 13:15 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: using possibly null timestamptz columns
2016-09-29 12:35 using possibly null timestamptz columns James Cloos <cloos@jhcloos.com>
2016-09-29 13:05 ` Re: using possibly null timestamptz columns Tom Lane <tgl@sss.pgh.pa.us>
2016-09-29 13:15 ` Re: using possibly null timestamptz columns Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-09-29 15:48 ` James Cloos <cloos@jhcloos.com>
0 siblings, 0 replies; 4+ messages in thread
From: James Cloos @ 2016-09-29 15:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
>>>>> "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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-09-29 15:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-09-29 12:35 using possibly null timestamptz columns James Cloos <cloos@jhcloos.com>
2016-09-29 13:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-09-29 13:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-09-29 15:48 ` James Cloos <cloos@jhcloos.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox