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
    I think this works: CHECK (NOT(period @> '-infinity'::timestamp with time zone)) and similar for +infinity – ypercubeᵀᴹ Oct 16 '13 at 13:12
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' )
);
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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