Foreign key on PostgreSQL range bounds

Question

Is it possible in PostgreSQL 9.6 to apply a foreign key constraint on the lower and upper bounds of a range (of integers in my case)?

Currently I keep the lower bound and upper bound in two columns, but would like to unify them into a range column while keeping the foreign key references.

Example

I have a table containing a list of sessions, something like:

CREATE TABLE sessions (
 session_id SERIAL PRIMARY KEY,
 session_start TIMESTAMPTZ NOT NULL,
 description TEXT
);

and then a table of groups of those sessions which is currently expressed as:

CREATE TABLE session_groups (
 group_id SERIAL PRIMARY KEY,
 first_session INTEGER NOT NULL UNIQUE,
 last_session INTEGER NOT NULL UNIQUE,
 description TEXT,
 FOREIGN KEY (first_session)
 REFERENCES sessions (session_id)
 ON UPDATE CASCADE
 ON DELETE RESTRICT,
 FOREIGN KEY (last_session)
 REFERENCES sessions (session_id)
 ON UPDATE CASCADE
 ON DELETE RESTRICT,
 CONSTRAINT last_session_after_first
 CHECK (last_session >= first_session)
);

What I would like:

CREATE TABLE session_groups (
 group_id SERIAL PRIMARY KEY,
 session_range INT4RANGE NOT NULL UNIQUE,
 description TEXT,
 FOREIGN KEY (lower(session_range))
 REFERENCES sessions (session_id)
 ON UPDATE CASCADE
 ON DELETE RESTRICT,
 FOREIGN KEY (upper(session_range))
 REFERENCES sessions (session_id)
 ON UPDATE CASCADE
 ON DELETE RESTRICT
 -- Dropped last_session_after_first constraint since
 -- the INT4RANGE type checks it automatically.
);

which is throwing a syntax error.

• ypercubeᵀᴹ: You know that these 2 designs are not equivalent. Did you mean to have UNIQUE constraints on both first_session and last_session?
• Matjaž: I know, it was just an example. The idea behind it is to avoid overlapping ranges and the usage of the ranges instead of two columns is meant to compensate the multiple UNIQUE contraints and triggers to check the overlapping.

Answer 1

It is not allowed to have arbitrary expressions in FOREIGN KEY constraints, only columns. That's why you get an error when you try the 2nd approach.

You can however, use a VIEW to access the table:

CREATE VIEW v_session_groups 
 (group_id, session_range, description) AS
SELECT
 group_id,
 int4range(first_session, last_session, '[)'),
 description
FROM 
 session_groups ;

You could even add triggers, and then be able to INSERT/UPDATE/DELETE from the view, as if it was a normal table.

---

Side notes:

• The first design has 2 UNIQUE constraints, on (first_session) and (last_session), while the 2nd has 1 UNIQUE constraint, on (first_session, last_session). These are not equivalent designs, so re-examine your requirements.
• I used the default '[)' (inclusive-exclusive) parameter for the int4range column. You may want to change that to '[]' (all-inclusive), depending on how you want to be saving ranges.
  Examples:int4range(1,1) and int4range(1,1,'[)') is the empty range while int4range(1,1,'[]') is the [1,2) range.

• If you want to enforce / avoid overlapping ranges, then you don't need a UNIQUE constraint at all but an EXCLUDE constraint:

  CREATE TABLE session_groups (
   group_id SERIAL PRIMARY KEY,
   first_session INTEGER NOT NULL,
   last_session INTEGER NOT NULL,
   description TEXT,
   FOREIGN KEY (first_session)
   REFERENCES sessions (session_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT,
   FOREIGN KEY (last_session)
   REFERENCES sessions (session_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT,
   CONSTRAINT non_overlapping_sessions
   EXCLUDE USING gist
   ( int4range(first_session, last_session, '[)') WITH && )
  );
  

  See also the documentation on constraints on range types. You probably need to add the extension as well:

  CREATE EXTENSION btree_gist;
  

• Matjaž: I had a trigger to check the overlapping, but dynamically generating the ranges for the constraint is something I did not think about. Thanks.