CODEWITHSUNDEEP
c#sqlpostgresqldatabase-designexclusion-constraint
kafka
kafka

Reputation: 733

How to ensure entries with non-overlapping time ranges?

I need to ensure my database only contains entries where two or more of its columns are unique. This can easily be achieved with a UNIQUE constraint over those columns.

In my case, I need to forbid duplication only for overlapping time ranges. The table has valid_from and valid_to columns. In some cases one might first need to expire the active entry to by setting valid_to = now, and then inserting a new entry adjusted to valid_from = now and valid_to = infinity.

I seem to be able to expire the prior entry without any problems using UPDATE, but inserting the new entry seems to be troublesome since my base columns are currently UNIQUE, and therefore can't be added again.

I thought of adding valid_from and valid_to as part of the UNIQUE constraint, but that would just make the constraint more loose, and allow duplicates and overlapping time ranges to exist.

How do I make a constraint to ensure that duplicates don't exist with overlapping valid_from and valid_to tsrange?

I seem to be looking for EXCLUDE USING GIST, but it does not seem to support multiple columns? This does not seem to work for me:

ALTER TABLE registration 
DROP Constraint IF EXISTS registration_{string.Join('_', listOfAttributes)}_key, 
ADD Constraint registration_{string.Join('_', listOfAttributes)}_key EXCLUDE USING GIST({string.Join(',', listOfAttributes)} WITH =, valid WITH &&);

Upvotes: 2

Views: 1614

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

You were on the right track. But the syntax for exclusion constraints is slightly different.

Depending on the undisclosed table definition, you may need to install the extension (additional module) btree_gist first. Once per db. It's needed for this solution to provide the required operator class for type integer:

CREATE EXTENSION btree_gist;

See:

Then:

CREATE TABLE registration  (
  tbl_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
, col_a  integer NOT NULL
, col_b  integer NOT NULL
, valid_from timestamp
, valid_to   timestamp
, CONSTRAINT no_overlap
    EXCLUDE USING gist (col_a with =, col_b with =, tsrange(valid_from, valid_to) WITH &&)
);

Each column needs to be listed with its respective operator.

And you need a range type. You mention separate columns valid_from and valid_to. And you also mention tsrange and valid in the failed command. That's confusing. Assuming two timestamp columns, an expression index with the expression tsrange(valid_from, valid_to) would do it.

Related:

Typically, timestamptz (tstzrange) should be chosen over timestamp (tsrange). See:

Maybe, a superior design would be a one-to-many relationship between your registration table and 1-N entries in a new registration_range table. And some logic to determine the currently valid entry (for any given point in time). Depends on more undisclosed information.

Upvotes: 3

Related Questions