PostgreSQL 9.2 tstzrange null/infinity 約束檢查

我想將 PostgreSQL 9.2 tstzrange 限制為兩端的有效日期。沒有 NULL 也沒有“無窮大”。

此 SQL 的各種修訂版不限制 ‘-/+infinity’ 輸入：

CREATE TABLE bill
(
 id serial NOT NULL,
 created_at timestamp with time zone NOT NULL DEFAULT now(),
 period tstzrange NOT NULL,
 -- other columns
 CONSTRAINT bill_pk PRIMARY KEY (id),
 CONSTRAINT bill_period_lower_not_null_ck CHECK (lower(period) IS NOT NULL),
 CONSTRAINT bill_period_upper_not_null_ck CHECK (upper(period) IS NOT NULL),
 CONSTRAINT bill_period_lower_not_infinity_ck CHECK (lower_inf(period) IS FALSE),
 CONSTRAINT bill_period_upper_not_infinity_ck CHECK (upper_inf(period) IS FALSE)
);


INSERT INTO bill (period) VALUES (tstzrange (NULL, NULL));
INSERT INTO bill (period) VALUES (tstzrange ('2000-05-10', '2015-02-22');
INSERT INTO bill (period) VALUES (tstzrange ('2000-05-10', now());
INSERT INTO bill (period) VALUES (tstzrange ('infinity', now());
INSERT INTO bill (period) VALUES (tstzrange ('-infinity', now());
INSERT INTO bill (period) VALUES (tstzrange (now(), 'infinity');


psql (9.2.3)
Type "help" for help.

test_1=> SELECT id, period FROM bill;
id |                           period
----+------------------------------------------------------------
 1 | ["2000-05-10 00:00:00+01","2015-02-22 00:00:00+00")
 2 | ["2000-05-10 00:00:00+01","2013-10-16 13:20:01.793803+01")
 4 | [-infinity,"2013-10-16 13:20:18.993038+01")
 5 | ["2013-10-16 13:21:14.208279+01",infinity)
(4 rows)

*_inf(period) IS FALSE將支票更改為TRUE拒絕有效日期。

手冊中的範圍函式

想法？

簡單的檢查按需要工作：

CHECK ( lower(period) > '-infinity' )

和

CHECK ( upper(period) < 'infinity' )

因此，您可以擁有：

CREATE TABLE bill
(
 id serial NOT NULL,
 created_at timestamp with time zone NOT NULL DEFAULT now(),
 period tstzrange NOT NULL,
 -- other columns
 CONSTRAINT bill_pk PRIMARY KEY (id),
 CONSTRAINT bill_period_lower_not_null_ck CHECK (lower(period) IS NOT NULL),
 CONSTRAINT bill_period_upper_not_null_ck CHECK (upper(period) IS NOT NULL),
 CONSTRAINT bill_period_lower_not_infinity_ck 
   CHECK ( lower(period) > '-infinity' ),
 CONSTRAINT bill_period_upper_not_infinity_ck 
   CHECK ( upper(period) < 'infinity' )
);

引用自：https://dba.stackexchange.com/questions/51597