Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'd like to limit a PostgreSQL 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 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)

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

Range functions in the manual

Thoughts?

share|improve this question
1  
I think this works: CHECK (NOT(period @> '-infinity'::timestamp with time zone)) and similar for +infinity – ypercubeᵀᴹ Oct 16 '13 at 13:12
up vote 3 down vote accepted

The simple checks work as desired:

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

and

CHECK ( upper(period) < 'infinity' )

Therefore, you can have:

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' )
);
share|improve this answer
    
Unfortunately, NULL is not excluded. i.e: This works: INSERT INTO bill (period) VALUES (tstzrange (NULL, NULL)); as does this: INSERT INTO bill (period) VALUES (tstzrange (NULL, now())); and this too: INSERT INTO bill (period) VALUES (tstzrange (now(), NULL)); – Craig R. Skinner Oct 16 '13 at 14:16
    
Oops, you are right. Not sure what I was checking - or thinking. – ypercubeᵀᴹ Oct 16 '13 at 14:20
    
Yes, after testing - those 4 now work together. Thanks. – Craig R. Skinner Oct 16 '13 at 14:27
    
You could combine them, with CHECK (lower(period) IS NOT NULL AND lower(period) > '-infinity') if you like. Or even all 4 in one CHECK constraint. – ypercubeᵀᴹ Oct 16 '13 at 14:32
    
Great minds think alike as I've just tested this single: 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') ) – Craig R. Skinner Oct 16 '13 at 14:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.