PostgreSQL 范围类型中的 NULL 与 `infinity` [英] NULL vs. `infinity` in PostgreSQL range types

问题描述

PostgreSQL 范围类型中的无穷大"是什么意思?将 infinity 或 -infinity 指定为界限或 NULL 之间有什么区别吗?IE.infinity 是指定范围限制为无限的显式形式，而 NULL 会隐式指定无限限制范围吗?

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?

请参阅以下示例:

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

推荐答案

更新:稍后再看，更好的解释:

Update: See this later, better explanation:

• 当范围类型中的 NULL 边界在功能上等效时，为什么 PostgreSQL 会认为它们与无限边界不同?

NULL 对 重叠做同样的事情运算符 && 分别为 -infinity 或 infinity.我在这里引用手册:

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

对任一边界使用 NULL 会导致该边的范围无界.

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

但是作为value，NULL还是有别于'infinity'！

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

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

返回 FALSE(不是 NULL，请注意！).

Returns FALSE (not NULL, mind you!).

更多内容请参见 SQLfiddle.

More in this SQLfiddle.

这篇关于PostgreSQL 范围类型中的 NULL 与 `infinity`的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！