Defining range CHECKs on INTERVAL columns (PostgreSQL)
I am currently designing a database that has to deal with a lot of temporal data. Consequently, there are quite a few columns which ought to hold time interval-like values. The important thing is that every one of these values has to fit into a fixed set of numerical ranges. Thus, creating database-level constraints to enforce these checks would make a lot of sense (and also provide some useful failsafes).
At first I considered using the BIGINT datatype & storing the intervals as UNIX timestamps. This would allow me to write a few simple CHECK constraints and just be done with it. However, investigating the PostgreSQL-specific INTERVAL datatype seemed to uncover some really helpful features. Furthermore, using it would probably make more sense in terms of my design's semantics.
The biggest problem I've come a across after switching to INTERVAL, though, is that I can't seem to find a nice and uniform way to define the CHECK constraints mentioned earlier.
Here's a rough example of what I'm trying to do:
CREATE TABLE PURCHASE( PURCHASE_ID SERIAL, PURCHASE_STATE_TYPE_ID SMALLINT NOT NULL, CUSTOMER_ID INTEGER NOT NULL, ... COOLINGOFF_PERIOD INTERVAL(3) NOT NULL, CONSTRAINT PK_PURCHASE PRIMARY KEY(PURCHASE_ID), CONSTRAINT FK_PURCHASE_PURCHASE_STATE_TYPE_ID FOREIGN KEY(PURCHASE_STATE_TYPE_ID) REFERENCES PURCHASE_STATE_TYPE(PURCHASE_STATE_TYPE_ID) ON UPDATE CASCADE, ... CONSTRAINT CHK_PURCHASE_COOLINGOFF_PERIOD_IN_RANGE CHECK((EXTRACT(EPOCH FROM INTERVAL COOLINGOFF_PERIOD)) BETWEEN 0 AND 315400000) ... );
In this case, we have the CHK_PURCHASE_COOLINGOFF_PERIOD_IN_RANGE CHECK constraint, which enforces the COOLINGOFF_PERIOD of each PURCHASE to fall between 0 and 10 years.
Unfortunately though, the above DDL statement fails with a syntax error: ERROR: syntax error at or near "COOLINGOFF_PERIOD".
Am I missing something here? Is there a nice and clean (i.e declarative) way to do this or should I go back to using BIGINT instead?
Technically, the syntax error in the check constraint is about this part:
EPOCH FROM INTERVAL COOLINGOFF_PERIOD
which is not accepted because in this context INTERVAL is meant to be followed by a literal, as in INTERVAL '10 days'
Anyway as suggested in the comments, the translation from EPOCH is not even needed, it's easier to write:
CREATE TABLE ... ( ... COOLINGOFF_PERIOD INTERVAL(3) NOT NULL CHECK (COOLINGOFF_PERIOD between '0'::interval and '10 years'::interval) ... );
See Interval Input in the doc for all the details and syntax variants.