Defining range CHECKs on INTERVAL columns (PostgreSQL)

Question

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?

What's wrong with between '0'::interval and '10 years'::interval? — Daniel Vérité
– Daniel Vérité, Commented Feb 1, 2014 at 15:12
As @Daniel wrote, use BETWEEN operator, take a look at this working demo: sqlfiddle.com/#!12/521a2 — krokodilko
– krokodilko, Commented Feb 1, 2014 at 15:44
@DanielVérité, @kordirko Thanks! That's exactly what I was looking for :). To tell the truth, I haven't used the INTERVAL type all that much before so I was having a bit of a brain fart regarding these constraints. The bit I was suprised about is that you can use the 'years' keyword in definition just like that! I tried all kinds of combinations beforehand and curiously couldn't get them to work. Now, if one of you could post your comment as a full answer, I'd be happy to accept it! — Priidu Neemre
– Priidu Neemre, Commented Feb 1, 2014 at 17:29

Daniel Vérité · Accepted Answer · 2014-02-02 14:55:08Z

2

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.

answered Feb 2, 2014 at 14:55

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Defining range CHECKs on INTERVAL columns (PostgreSQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related