关于PostgreSQL的范围类型
從PostgreSQL 9.2開始，我們可以使用內置的範圍類型。因為這個新功能相當方便，所以我想介紹一下。由於這大致上只是對手冊中的內容進行介紹，所以如果追求準確性的話，建議閱讀手冊。
这是什么东西？
大略/粗略地
可以将”1到10″或者”2013年1月1日到2013年12月31日”这样的数据作为一个元素进行处理。
嵌入式类型
最初已经预先准备好的类型有 int4range、int8range、numrange、tsrange、tstzrange和daterange 这六种。除此之外，用户还可以定义新的范围类型。
字面表达
没有文字的字面值，在这种情况下，您可以使用构造函数或将其转换为字符串。当我硬编码时，我会使用强制转换，但如果要使用元素标识符，我认为使用构造函数更合适。
-- 1 以上 10 以下
SELECT int4range(1, 10, '[]');
-- 1 以上 10 以下
SELECT '[1,10]'::int4range;
多么方便呢
作为对于「开始」和「结束」的一个解决方案，这是令人困扰的。
根据表格设计的结果，您对下面列出的情况有任何了解吗？
或者
这两种情况都会影响心灵，前者是因为允许存在空的元素。后者则会变成“9999 年，哈哈”，但作为现实中常见的结果，这是很合理的吧。
使用范围类型，将得到以下表格。
有了这个，可以毫无顾虑地添加NOT NULL约束，并且不必做出令人痛苦的选择，例如插入超过遥远未来的日期。
作为指示索引的利用
不过，我认为将已存在的系统替换为区域类型可能会很困难。因此，我将介绍一种方法，在保持现有表定义不变的情况下享受区域类型的好处。
我会在刚刚出现的这张表（假设为users）上添加一个新的索引。
CREATE INDEX users_term_index ON users USING gist(daterange(start_date, end_date, '[]'));
使用了一个称为索引的机制来表示表达式。在使用范围操作符的查询中，可以获得速度上的优势。同时，我认为可读性也会提高。请参阅下面的内容。
-- start_date から end_date が 2012-08-01 から 2013-02-01 までの間と一部でも重なるユーザー
SELECT name
FROM users
WHERE start_date <= '2013-02-01'::DATE
AND end_date >= '2012-08-01'::DATE;
-- 上記を範囲型で表現
SELECT name
FROM users
WHERE daterange(start_date, end_date, '[]') && daterange('2012-08-01', '2013-02-01', '[]');
每次在写表达重叠范围的条件时，我总是需要在纸上画图才能有自信地写出前者那样的查询。所以能够使用后者的写法对我来说是幸运的。如果采用前者的写法，像 btree (start_date DESC, end_date) 这样可疑的索引可以通过 SET enable_seqscan TO off 等强制使用的方式来加快速度，但后者可以获得与此相近的速度。（请注意，这取决于情境，不要盲目相信）
但需要注意的是，表达式索引的更新成本较大，所以需要根据提取和更新的比例仔细考虑是否采用。
限制条件
在表格定义中，可以使用范围类型作为约束。
CREATE TABEL users(
name text,
term daterange,
exclude using gist(term with &&) -- テーブル内での重複した期間を許さない
);
按照上述定义的表格，将会如下所示地运行。
insert into users (name, term) values ('hoge', '[2013-01-01,2013-02-01)'::daterange);
insert into users (name, term) values ('hoge', '[2013-02-01,2013-03-01)'::daterange);
-- ここまでは正常、以下は name がどうあれ範囲に重複があるので制約違反
insert into users (name, term) values ('fuga', '[2013-02-28,2013-03-31)'::daterange);
另外，通过使用btree_gist扩展，即使在非范围类型的数据上，也可以通过使用运算符来设置约束。
CREATE EXTENSION btree_gist; -- CREATE EXTENSION 可能なアカウントにて実行
CREATE TABEL users(
name text,
term daterange,
exclude using gist(name with =, term with &&) -- ユーザー毎に重複した期間を許さない
);
按照上述的定义，表格将以下述方式工作。
insert into users (name, term) values ('hoge', '[2013-01-01,2013-02-01)'::daterange);
insert into users (name, term) values ('hoge', '[2013-02-01,2013-03-01)'::daterange);
insert into users (name, term) values ('fuga', '[2013-01-01,2013-02-01)'::daterange);
-- ここまでは正常、以下は name が等しくて範囲に重複がある行があるので制約違反
insert into users (name, term) values ('hoge', '[2013-02-28,2013-03-31)'::daterange);
请留意事项
「封閉的界線」和「開放的界線」
在处理范围类型时需要注意”闭合边界”和”开放边界”。但实际上，这只是关于”大于等于、小于等于、大于、小于”的讨论。
让我们看看构造函数 int4range(1, 10, ‘[]’) 的用法示例中的第三个参数。这里有一个字符串'[]’，它指定了边界的开合部分。
总结如下:
‘[‘ = 大于等于
‘]’ = 小于等于
‘(‘ = 大于
‘)’ = 小于
在这种情况下，我们将”大于等于”和”小于等于”称为闭合边界，将”大于”和”小于”称为开放边界。
另外，当省略构造函数的第三个参数时，默认为 ‘[)’，即”大于等于、小于”。此外，在使用范围类型定义表并使用 psql 等工具进行 SELECT 操作时，结果显示将以类似 [1, 11) 的”大于等于、小于”格式显示。
我习惯于在 SQL 的 BETWEEN 子句中使用闭合边界来设置起点和终点，但范围类型的基本规则是'[)’，即”大于等于、小于”。所以最好先认识到这一点，这样就不容易陷入困境。
‘[)’ 样式在 numrange 或 tsrange 的情况下特别有优势。
-- false
SELECT tsrange('2013-01-01 00:00:00', '2014-01-01 00:00:00', '[)') @> '2014-01-01 00:00:00'::timestamp;
-- true
SELECT tsrange('2013-01-01 00:00:00', '2014-01-01 00:00:00', '[]') @> '2014-01-01 00:00:00'::timestamp;
如果试图使用封闭边界来描述上述这样的例子的终点，
--- 0.009 秒超過したので false
SELECT tsrange('2013-01-01 00:00:00', '2013-12-31 23:59:59.99', '[]') @> '2013-12-31 23:59:59.999'::timestamp;
--- 丁度境界値なので true
SELECT tsrange('2013-01-01 00:00:00', '2013-12-31 23:59:59.99', '[]') @> '2013-12-31 23:59:59.99'::timestamp;
这样说来，对于定义非常精确的“准确性”确实有点困难呢。相比而言，“以上・未满”风格更简洁明了。关于更详细的内容，请参考手册中关于离散范围类型的部分，那里有更为详尽的解释。
总结
使用范围型可以方便地编写像下面这样的查询，例如”按每个月计算活跃期间用户的数量”。
WITH months AS (
SELECT to_char("day", 'YYYY年MM月') AS "month"
,daterange("day"::DATE, ("day" + '1 month'::interval)::DATE, '[)') AS term
FROM generate_series('2013-01-01', '2013-12-01', '1 month'::INTERVAL) AS "day")
-- 各月の表
-- month | term
--------------+-------------------------
-- 2013年01月 | [2013-01-01,2013-02-01)
-- 2013年02月 | [2013-02-01,2013-03-01)
-- 2013年03月 | [2013-03-01,2013-04-01)
-- 2013年04月 | [2013-04-01,2013-05-01)
-- 2013年05月 | [2013-05-01,2013-06-01)
-- 2013年06月 | [2013-06-01,2013-07-01)
-- 2013年07月 | [2013-07-01,2013-08-01)
-- 2013年08月 | [2013-08-01,2013-09-01)
-- 2013年09月 | [2013-09-01,2013-10-01)
-- 2013年10月 | [2013-10-01,2013-11-01)
-- 2013年11月 | [2013-11-01,2013-12-01)
-- 2013年12月 | [2013-12-01,2014-01-01)
-- ユーザーテーブルと期間の重なりを条件に結合して集計
SELECT months."month"
,count(users.name)
FROM months
JOIN users ON users.term && months.term
GROUP BY months."month"
;
通过使用范围类型，可以直观地编写类似于“对范围的范围条件”的查询，这样可以避免混乱和bug。对于特定的领域来说，应该会比以前更适合表达，建议您考虑一下。但是请注意，在程序员的SQL的最后一节（39.15）中写着“不要相信新功能”，因此在引入新功能时，建议进行性能测量和操作确认。
另外，我也写了一篇关于如何在Rails应用程序中使用PostgreSQL范围类型的文章，如果您感兴趣的话，也请务必查看一下。
以上述完。
順便一提
今天是我的生日！非常感谢！
