2

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?

1
  • 1
    I think this works: CHECK (NOT(period @> '-infinity'::timestamp with time zone)) and similar for +infinity Oct 16, 2013 at 13:12

1 Answer 1

3

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' )
);
5
  • 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)); Oct 16, 2013 at 14:16
  • Oops, you are right. Not sure what I was checking - or thinking. Oct 16, 2013 at 14:20
  • Yes, after testing - those 4 now work together. Thanks. Oct 16, 2013 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. Oct 16, 2013 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') ) Oct 16, 2013 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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