I recently discovered that a time range can be empty which appears to be different vs a null field.
db=# select tstzrange(now(), now());
tstzrange
-----------
empty
(1 row)
db=# select tstzrange(now(), now()) is null;
?column?
----------
f
(1 row)
How can I match an empty field for a query?
Compare it with a string 'empty'
select tstzrange(now(), now()) = 'empty' as out
which produces:
out
-----
t
While for a non-empty result with query like:
select tstzrange(now(), now() + interval '1d') = 'empty' as out
it consistently produces:
out
-----
f
In fact implicit conversion of 'empty'::tstzrange takes place.
Read more on the matter in documentation
tstzrange type eval against a string 'empty'?
'empty' to tstzrange type.use isempty()
select isempty(tstzrange(now(), now())); -- true
select isempty(tstzrange(now(), now() + interval '1d')); -- false
