Thread: PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
From
skinner@britvault.co.uk (Craig R. Skinner)
Date:
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
Re: PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
From
skinner@britvault.co.uk (Craig R. Skinner)
Date:
On 2013-10-16 Wed 13:50 PM |, Craig R. Skinner wrote: > I'd like to limit a 9.2 tstzrange to valid dates at both ends. > No NULLs nor '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), This works: CONSTRAINT bill_period_not_null_nor_infinity_ck CHECK ( lower(period) IS NOT NULL AND upper(period) IS NOT NULL AND (lower(period) > '-infinity') AND (upper(period) < 'infinity') ) Cheers, -- Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7