Return to Answer

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 && )
    );
  

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;
  

• 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.

• 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 && )
    );
  

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 note:

• 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.

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.