Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

NULL vs. `infinity` in PostgreSQL range types

What is the meaning of 'infinity' in PostgreSQL range types? Is there any difference between specifying infinity or -infinity as a bound, or NULL? I.e. is infinity an explicit form of specifying that the range bound is infinite, whereas NULL would implicit specify an infinite bound range?

See the following examples:

SELECT tstzrange('-infinity','infinity') && tstzrange(NULL, NULL);
 ?column?
----------
 t

SELECT tstzrange('2013-01-01 00:00:00+01', '2013-02-01 00:00:00+01')
    && tstzrange(NULL, '2013-03-01 00:00:00+01');
 ?column?
----------
 t

SELECT tstzrange('2013-01-01 00:00:00+01', '2013-02-01 00:00:00+01')
    && tstzrange('-infinity', '2013-03-01 00:00:00+01');
 ?column?
----------
 t
like image 997
Cochise Ruhulessin Avatar asked Mar 22 '13 19:03

Cochise Ruhulessin


1 Answers

Update: See this later, better explanation:

  • Why does PostgreSQL consider NULL boundaries in range types to be distinct from infinite boundaries when they're functionally equivalent?

NULL does the same thing for the overlap operator && as -infinity or infinity, respectively. I quote the manual here:

Using NULL for either bound causes the range to be unbounded on that side.

But as value, NULL is still distinct from 'infinity'!

SELECT tstzrange('-infinity','infinity') = tstzrange(NULL, NULL);

Returns FALSE (not NULL, mind you!).

More in this SQLfiddle.

like image 137
Erwin Brandstetter Avatar answered Sep 22 '22 10:09

Erwin Brandstetter