How Can PostgreSQL's EXCLUDE Constraint Prevent Overlapping and Adjacent Date/Time Range Entries?

Preventing Adjacent/Overlapping Entries with EXCLUDE in PostgreSQL

Problem:

When creating a database to store arbitrary date/time ranges, the goal is to prevent overlapping and adjacent entries. A constraint using an EXCLUDE clause and a GiST index is employed, but concerns arise about the assumption of second resolution.

Solution 1: Assuming Second Resolution

If the application only requires second resolution, assuming this in the constraint may be sufficient. However, higher resolution data types might require a more robust solution.

Solution 2: Inclusive Bounds

Inclusive bounds ([]) for both the lower and upper range allow overlap detection by a CHECK constraint using range functions. An EXCLUDE constraint with && ensures non-overlap, while another CHECK constraint enforces inclusive bounds.

Solution 3: Canonical Bounds

Canonical bounds ([)] facilitate non-overlap through an EXCLUDE constraint with &&. Additionally, an adjacent operator (-|-) is used in a separate EXCLUDE constraint to prohibit adjacent entries. A CHECK constraint ensures appropriate bound values.

Implementation Details:

-- Solution 2: Inclusive Bounds
CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY,
  tsr tsrange,
  CONSTRAINT tsr_no_overlap  EXCLUDE USING gist (tsr WITH &&),
  CONSTRAINT tsr_enforce_incl_bounds CHECK (lower_inc(tsr) AND upper_inc(tsr))
);

-- Solution 3: Canonical Bounds
CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY,
  tsr tsrange,
  CONSTRAINT tsr_no_overlap  EXCLUDE USING gist (tsr WITH &&),
  CONSTRAINT tsr_no_adjacent EXCLUDE USING gist (tsr WITH -|-),
  CONSTRAINT tsr_enforce_bounds CHECK (lower_inc(tsr) AND NOT upper_inc(tsr))
);

Recommendation:

Solution 3 offers a robust solution for handling both overlapping and adjacent entries, regardless of the resolution requirement.

The above is the detailed content of How Can PostgreSQL's EXCLUDE Constraint Prevent Overlapping and Adjacent Date/Time Range Entries?. For more information, please follow other related articles on the PHP Chinese website!