PostgreSQL Temporal Constraints with WITHOUT OVERLAPS

Prevent overlapping room bookings

create extension if not exists btree_gist;

create table room_bookings (
  booking_id bigint generated always as identity,
  room_id bigint not null,
  reserved_at tstzrange not null,
  guest_name text not null,
  primary key (room_id, reserved_at without overlaps),
  unique (booking_id)
);

The scalar room_id needs GiST equality support from btree_gist. Empty ranges are not allowed in a WITHOUT OVERLAPS key.

Choose range boundaries deliberately

A half-open range such as [start, end) includes the start and excludes the end. That lets one booking end at 11:00 and the next begin at 11:00 without overlap. Closed ranges would make those two bookings collide.

Use tstzrange when the instants represent real-world times across time zones. Use daterange for whole-day periods and tsrange only when a timestamp without time zone is genuinely the domain value.

Insert adjacent, non-overlapping bookings

insert into room_bookings (room_id, reserved_at, guest_name)
values
  (7, tstzrange('2026-08-01 10:00Z', '2026-08-01 11:00Z', '[)'), 'Ada'),
  (7, tstzrange('2026-08-01 11:00Z', '2026-08-01 12:00Z', '[)'), 'Linus');

Let the constraint reject races

WITHOUT OVERLAPS is enforced by a GiST-backed constraint. Two concurrent inserts cannot both create overlapping ranges for the same scalar key and commit successfully.

Keep a friendly preflight check in the application if it improves the form, but treat the database error as a normal concurrency outcome and translate it into a useful message.

This conflicting booking fails

insert into room_bookings (room_id, reserved_at, guest_name)
values (
  7,
  tstzrange('2026-08-01 10:30Z', '2026-08-01 11:30Z', '[)'),
  'Grace'
);

Use PERIOD for complete time coverage

A temporal foreign key compares ordinary key columns for equality and requires the parent's combined PERIOD ranges to cover the child's entire range. The referenced key must end with a range column declared WITHOUT OVERLAPS.

This is useful for assignments that must fall inside an employee's active contract periods or prices that must be covered by a product's valid catalog periods. Temporal foreign keys have restrictions on referential actions, so check the PostgreSQL 18 CREATE TABLE documentation before choosing cascades.

Temporal foreign-key shape

foreign key (employee_id, period active_at)
  references employee_contracts
  (employee_id, period active_at)

Migrate existing start and end columns carefully

Add a range column, backfill it with the intended boundary convention, reject invalid or empty periods, and query for existing overlaps before adding the constraint. Existing dirty data will prevent the key from being created.

Keep the old columns only if another interface still requires them. Two independent representations of the same time span can drift unless one is generated from the other.

Model the rule where every writer sees it

A schema constraint protects migrations, background jobs, scripts,\n+ APIs, and manual edits. Use the PostgreSQL schema designer to sketch the tables, then keep the final temporal constraint in\n+ version-controlled SQL migrations.\n+