Ensuring adjacent ranges in PostgreSQL

"Inclusion constraint" is different from Exclusion constraint

There is no "Inclusion Constraint" in Postgres that would act as the inversion of an EXCLUDE constraint. The requirement is logically different. Exclusion is based on a single row violating the constraint, while inclusion has to be verified against the whole table - which is a lot more costly and complex to implement.

Your requirement demands the existence of any row that checks a given requirement. There is a tiny gap between the time of the check and the time of committing the INSERT. To be absolutely sure, you'll have to lock the required row, so no concurrent transaction can update or delete it in between.

Canonical form for discrete range types

The manual:

The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [).

So the (exclusive) upper bound of daterange '[2026-01-01,2026-12-31]' is 2027-01-01 - even though your input uses 2026-12-31 as inclusive upper bound.

This is why simply comparing upper to lower bound produces a match for adjacent rows. The lower value (being the upper, exclusive bound of the previous range) is incremented by 1.

Related:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

btreegist

CREATE EXTENSION btreegist;

See:

• How to use uuid with postgresql gist index type?

Trigger solution

Race conditions and edge cases

We need to allow the entry of the first row per id, which has no neighbor yet.

There are inherent race condition between transactions that want to insert rows concurrently.

• I eliminated the more common one for adding an entry to an existing id with a SHARE lock below.
• I left the less common (but more expensive) one open, where a new ID is started. See comments in code.

Of course, the whole regime falls apart as soon as you allow DELETE or UPDATE that changes key columns. This solution assumes you have safely excluded both.

Trigger function:

CREATE OR REPLACE FUNCTION whatevernogap()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF EXISTS (SELECT FROM whatever w WHERE w.id = NEW.id) THEN
   --  1st entry for id is always allowed, so skip check if no row for id
   --  Leaves RACE CONDITION 1 open:
   --     multiple transactions might enter the first row for the same id concurrently and evade the gap check below
   -- Can't be fixed without a more heavy-handed lock - like an exclusive lock on the id in a parent table
      PERFORM FROM whatever w        -- SELECT list can stay empty; we only care for existence
      WHERE  w.id = 1
      AND   (upper(w.valid) = lower(NEW.valid)
          OR lower(w.valid) = upper(NEW.valid))
      LIMIT  1                       -- optional - but possibly helps optimize query plan
      FOR    SHARE;                  -- prevents RACE CONDITION 2 for existing ranges
      IF NOT FOUND THEN
         RAISE EXCEPTION 'New row would create a gap in column "valid" for given "id" %.', NEW.id;
      END IF;
   END IF;
   RETURN NEW;
END
$func$;

Trigger:

CREATE TRIGGER trginsbef
BEFORE INSERT ON whatever
FOR EACH ROW
EXECUTE FUNCTION whateverno_gap();

fiddle

Another option that come to mind: a dirty CHECK constraint (with similar race conditions). Related:

• Disable all constraints and table checks while restoring a dump