PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK

Поиск
Список
Период
Сортировка
От skinner@britvault.co.uk (Craig R. Skinner)
Тема PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
Дата
Msg-id 20131016125014.GA25474@teak.britvault.co.uk
обсуждение исходный текст
Ответы Re: PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK  (skinner@britvault.co.uk (Craig R. Skinner))
Список pgsql-sql
I'd like to limit a 9.2 tstzrange to valid dates at both ends.
No NULLs nor 'infinity'.

Various revisions of this SQL isn't constraining '-/+infinity' input:

CREATE TABLE bill
( id serial NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), period tstzrange NOT NULL, -- other
columnsCONSTRAINT bill_pk PRIMARY KEY (id), CONSTRAINT bill_period_lower_not_null_ck CHECK (lower(period) IS NOT NULL),
CONSTRAINTbill_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)

Changing the *_inf(period) IS FALSE checks to TRUE rejects valid dates.

Range functions in the manual: http://www.postgresql.org/docs/9.2/static/functions-range.html

Thoughts?
-- 
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7



В списке pgsql-sql по дате отправления:

Предыдущее
От: skinner@britvault.co.uk (Craig R. Skinner)
Дата:
Сообщение: Re: Many to many link tables with history?
Следующее
От: skinner@britvault.co.uk (Craig R. Skinner)
Дата:
Сообщение: Re: PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK