Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer 1

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
add comment

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.