Preventing Adjacent/Overlapping Entries with Exclude in Postgresql

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Range types consist of lower and upper bound, and each can be included or excluded.

The canonical form (and default for range types) is to include the lower and exclude the upper bound.

Inclusive bounds `'[]'`

You could include lower and upper bound ([]), and enforce it with a CHECK constraint using range functions.

Then "adjacent" ranges overlap. Excluding overlapping ranges seems clear. There is a code example in the manual.

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))  -- all bounds inclusive!
);

Only ranges with inclusive bounds are allowed:

INSERT INTO tbl(tsr) VALUES ('[2013-10-22 00:00, 2013-10-22 01:00]');

db<>fiddle here

Canonical bounds `'[)'`

Enforce [) bounds (including lower and excluding upper).

In addition, create another exclusion constraint employing the adjacent operator -|- to also exclude adjacent entries. Both are based on GiST indexes as GIN is currently not supported for this.

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

db<>fiddle here

_{Old sqlfiddle}

Unfortunately, this creates two identical GiST indexes to implement both exclusion constraints, where one would suffice, logically.

How to ensure entries with non-overlapping time ranges?

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:

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
How to use (install) dblink in PostgreSQL?

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.

Perform this hours of operation query in PostgreSQL
Non-overlap, continuous timestamp ranges (tstzrange) for opening hours
Postgresql 9.4 query gets progressively slower when joining TSTZRANGE with &&
Store the day of the week and time?

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

Ignoring time zones altogether in Rails and PostgreSQL
Don't use timestamp (without time zone)

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.

OVERLAPS operator to check multiples rows

The immediate cause for the error message is that the plpgsql IF statement expects a single boolean expression, while your code returns a whole set of boolean expressions.

IF(startdatum1, einddatum1) OVERLAPS (startdatum, einddatum) FROM  
rooster THEN

is an ugly short form of:

IF (SELECT(startdatum1, einddatum1) OVERLAPS (startdatum, einddatum)
    FROM  rooster) THEN

and simply invalid this way. You would have to agggregate or use EXISTS:

IF EXISTS (
   SELECT 1 FROM  rooster r
   WHERE  (startdatum1, einddatum1) OVERLAPS (r.startdatum, r.einddatum)
   ) THEN ...

More importantly, an exclusion constraint is almost certainly the better solution for your problem (requires Postgres 9.0+):

Postgres constraint for unique datetime range
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Non-overlap, continuous timestamp ranges (tstzrange) for opening hours

The answer to 1. is clear. To make sure there is no overlap use an exclusion constraint:

CREATE TABLE operating_period (
  id serial PRIMARY KEY                -- PK is NOT NULL automatically
 ,during tstzrange NOT NULL
 ,EXCLUDE USING gist (during WITH &&)  -- no overlap
);

This is implemented with a GiST index on during, that supports many types of queries automatically. Related answer:

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
Perform this hours of operation query in PostgreSQL

Answers to 2. and 3. are not as clear, because it really depends on a lot of things. Both have their pros and cons. For opening hours I would most likely go with range types in current versions of Postgres. I would also enforce [) bounds for all entries to keep things simple. Details in the first linked answer.

If you should go with (start_at, end_at), you'll be interested in the OVERLAPS operator:

Getting results between two dates in PostgreSQL
Find overlapping date ranges in PostgreSQL

_{Either way, the guideline here is to ask one question per question, not a whole list ...}

one-to-many' relation integrity issue for time ranges

I need to be sure, that for a certain timestamp, there will be only one row in table foo for foo.barid

And by "timestamp" you seem to mean a certain period of time.

An exclusion constraint on a range type, combined with equality on barid (utilizing the additional module btree_gist) would be the perfect solution.

CREATE EXTENSION btree_gist;  -- needed once per database

CREATE TABLE foo (
  fooid  serial PRIMARY KEY
, barid  integer NOT NULL REFERENCES bar(barid) 
, bazid  integer NOT NULL REFERENCES baz(bazid)
, time_range tsrange NOT NULL           -- replaces startdate  & enddate 
, EXCLUDE USING gist (barid WITH =, time_range WITH &&)
);

This requires Postgres 9.2 or later.

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

The manual has a matching code example!

Postgres constraint for unique datetime range

You can keep your separate timestamp columns and still use an exclusion constraint on an expression:

CREATE TABLE tbl (
   tbl_id    serial PRIMARY KEY
 , starts_at timestamp
 , ends_at   timestamp
 , EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&)  -- no overlap
);

Constructing a tsrange value without explicit bounds as tsrange(starts_at, ends_at) assumes default bounds: inclusive lower and exclusive upper - '[)', which is typically best.

db<>fiddle here

_{Old sqlfiddle}

Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

Add constraint to existing table

ALTER TABLE tbl ADD CONSTRAINT tbl_no_overlapping_time_ranges
EXCLUDE USING gist (tsrange(starts_at, ends_at) WITH &&)

Syntax details are the same as for CREATE TABLE.

Preventing Adjacent/Overlapping Entries with Exclude in Postgresql