Prevent Double Booking in PostgreSQL with Constraints

The most reliable way to stop double bookings is to let PostgreSQL refuse them. In this post I build a booking table where an overlapping reservation for the same room, chair or doctor is impossible at the database level, then handle the resulting error cleanly in a NestJS service. Everything here is written for PostgreSQL 16 and 17.

What is a double booking, technically?#

A double booking is two rows that claim the same resource for time ranges that overlap. "Overlap" is the important word. Two appointments from 09:00 to 09:30 and 09:15 to 09:45 never share a start time, so a unique index on (resource_id, starts_at) does nothing to stop them.

Most first attempts at a booking system look like this:

1. The API receives a request for 09:15 to 09:45.
2. It runs a SELECT to see whether any booking overlaps that window.
3. If nothing comes back, it runs an INSERT.

This works in development and fails in production. Two requests arrive a few milliseconds apart, both run step 2 before either has run step 3, both see an empty result, and both insert. PostgreSQL's default isolation level, Read Committed, does not protect you here, because neither transaction can see the other's uncommitted row.

How do I store a booking time slot?#

Store the slot as a single tstzrange column instead of two separate timestamp columns. A range type is a value that holds a lower bound, an upper bound and whether each end is inclusive. PostgreSQL ships with range operators, so "do these two slots overlap?" becomes the && operator instead of four comparisons you have to get right.

Use the half-open form [start, end). It includes the start and excludes the end, which means a booking from 09:00 to 09:30 and another from 09:30 to 10:00 do not overlap. That is exactly how people think about back-to-back appointments. The tstzrange(a, b) constructor uses [) by default.

Use tstzrange, not tsrange. The tz version stores absolute instants, so clinics in different time zones and daylight saving changes do not corrupt your data.

How does an exclusion constraint prevent double booking?#

An exclusion constraint is a rule that says "no two rows may have values where all of these operators return true". A unique constraint is a special case of it that only knows the = operator. With an exclusion constraint you can say: no two rows where resource_id is equal and during overlaps.

Exclusion constraints are enforced through an index, and the index must support every operator you use. Range overlap needs a GiST index. Plain equality on a bigint is not something GiST supports out of the box, which is why you need the btree_gist extension. It adds GiST operator classes for ordinary scalar types.

Here is the full schema:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE resources (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE bookings (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  resource_id bigint NOT NULL REFERENCES resources (id),
  customer_id bigint NOT NULL,
  during      tstzrange NOT NULL,
  status      text NOT NULL DEFAULT 'confirmed'
              CHECK (status IN ('pending', 'confirmed', 'cancelled')),
  created_at  timestamptz NOT NULL DEFAULT now(),

  -- Every slot must be a non-empty, bounded, half-open range: [start, end)
  CONSTRAINT bookings_during_valid CHECK (
    NOT isempty(during)
    AND NOT lower_inf(during)
    AND NOT upper_inf(during)
    AND lower_inc(during)
    AND NOT upper_inc(during)
  ),

  -- No two active bookings for the same resource may overlap
  CONSTRAINT bookings_no_overlap
    EXCLUDE USING gist (resource_id WITH =, during WITH &&)
    WHERE (status <> 'cancelled')
);

A few details matter here:

• The CHECK constraint rejects empty ranges, open-ended ranges and odd bounds such as (start, end]. Without it, a client could send an unbounded range and block a resource forever.
• The WHERE (status <> 'cancelled') clause makes it a partial constraint. Cancelled bookings stay in the table for history, but they no longer hold the slot.
• The constraint creates its own GiST index. You do not need a second index for overlap lookups on (resource_id, during).

The official reference for this syntax is the CREATE TABLE page on EXCLUDE, and the extension itself is documented on the btree_gist page.

What happens when two bookings overlap?#

PostgreSQL rejects the second row with SQLSTATE 23P01, named exclusion_violation. Try it:

INSERT INTO resources (name) VALUES ('Chair 1');

INSERT INTO bookings (resource_id, customer_id, during)
VALUES (1, 101, tstzrange('2026-12-10 09:00+05', '2026-12-10 09:30+05'));

-- Back-to-back is fine: [09:30, 10:00) does not overlap [09:00, 09:30)
INSERT INTO bookings (resource_id, customer_id, during)
VALUES (1, 102, tstzrange('2026-12-10 09:30+05', '2026-12-10 10:00+05'));

-- Overlaps the first booking, so this fails
INSERT INTO bookings (resource_id, customer_id, during)
VALUES (1, 103, tstzrange('2026-12-10 09:15+05', '2026-12-10 09:45+05'));

The last statement fails with an error like this:

ERROR:  conflicting key value violates exclusion constraint "bookings_no_overlap"
DETAIL:  Key (resource_id, during)=(1, ["2026-12-10 04:15:00+00","2026-12-10 04:45:00+00")) conflicts with existing key (resource_id, during)=(1, ["2026-12-10 04:00:00+00","2026-12-10 04:30:00+00")).

The important part is that this also holds under concurrency. If two transactions insert overlapping rows at the same time, the second one waits for the first. When the first commits, the second fails with 23P01. When the first rolls back, the second succeeds. There is no window where both get through.

How do I handle the 23P01 error in NestJS?#

Catch the database error in your service, check the SQLSTATE code and the constraint name, and throw a ConflictException so the client gets an HTTP 409. Do not match on the error message text; it can change between versions and locales.

This example uses the pg driver (node-postgres 8) with a pool provided under a custom token. The same idea works with TypeORM or Prisma, as long as you can reach the underlying error code.

import { BadRequestException, ConflictException, Inject, Injectable } from "@nestjs/common";
import type { Pool } from "pg";

export const PG_POOL = Symbol("PG_POOL");

interface PgError {
  code: string;
  constraint?: string;
}

function isPgError(err: unknown): err is PgError {
  return (
    typeof err === "object" &&
    err !== null &&
    "code" in err &&
    typeof (err as { code: unknown }).code === "string"
  );
}

export interface CreateBookingInput {
  resourceId: number;
  customerId: number;
  startsAt: Date;
  endsAt: Date;
}

export interface BookingRow {
  id: string;
  resource_id: string;
  during: string;
  status: string;
}

@Injectable()
export class BookingsService {
  constructor(@Inject(PG_POOL) private readonly pool: Pool) {}

  async create(input: CreateBookingInput): Promise<BookingRow> {
    try {
      const { rows } = await this.pool.query<BookingRow>(
        `INSERT INTO bookings (resource_id, customer_id, during)
         VALUES ($1, $2, tstzrange($3, $4, '[)'))
         RETURNING id, resource_id, during::text AS during, status`,
        [input.resourceId, input.customerId, input.startsAt, input.endsAt],
      );
      return rows[0]!;
    } catch (err) {
      if (isPgError(err) && err.code === "23P01" && err.constraint === "bookings_no_overlap") {
        throw new ConflictException("This time slot is already booked.");
      }
      if (isPgError(err) && err.code === "23514") {
        // check_violation: bad range, e.g. end before start
        throw new BadRequestException("The booking time range is not valid.");
      }
      throw err;
    }
  }
}

Note that bigint columns come back from pg as strings by default, which is why id is typed as string. If you validate the request body with a DTO and class-validator, also check that endsAt is after startsAt there, so most bad input never reaches the database. The CHECK constraint is the safety net, not the first line of defence.

On the front end, treat a 409 as a normal outcome rather than a crash: tell the user the slot was just taken, refresh the calendar for that day, and keep the rest of the form filled in so they can pick another time.

How do I find free slots for a calendar view?#

Query the bookings that overlap the visible day and let the front end grey them out. The same GiST index from the exclusion constraint serves this query:

SELECT id, lower(during) AS starts_at, upper(during) AS ends_at
FROM bookings
WHERE resource_id = $1
  AND status <> 'cancelled'
  AND during && tstzrange($2::timestamptz, $3::timestamptz, '[)')
ORDER BY lower(during);

Showing free slots is a user-experience feature. It does not replace the constraint, because the calendar can be stale by the time the user clicks "Book".

Can I use ON CONFLICT with an exclusion constraint?#

Only partly. INSERT ... ON CONFLICT DO NOTHING without a conflict target works with exclusion constraints, and the insert silently returns zero rows when the slot is taken. ON CONFLICT DO UPDATE does not support exclusion constraints as the arbiter. In practice I prefer letting the insert fail and returning a 409, because the client needs to know the booking did not happen.

What about other ways to stop double booking?#

There are three common options, and the exclusion constraint is the one I reach for first. Here is how they compare:

Locking the parent row is a fine choice when you need more than overlap checks, such as a daily booking limit per customer. I cover that pattern in the post on PostgreSQL transactions and race conditions.

One more option is coming into view. PostgreSQL 18 added temporal primary keys and unique constraints with WITHOUT OVERLAPS, which express the same rule more directly. If you are on 16 or 17, the exclusion constraint above is the tool, and it keeps working after an upgrade.

How does this fit into a real booking app?#

The exclusion constraint is the core of the schema in my booking system concept project, where a chair, a room or a member of staff can only be in one appointment at a time. You can try it in my booking system demo, where the database itself blocks a second booking for a slot that is already taken. If you want to see the rest of my database work, the PostgreSQL skill page lists related projects, and I build the API side with NestJS.

Things I would add next in a real app:

1. A staff_id exclusion constraint alongside the resource one, if both the room and the person must be free.
2. A pending status with an expiry job, so unpaid holds release the slot after 10 minutes.
3. Buffer time between appointments, done by storing the padded range in during and the visible times in separate columns.

What should you do first?#

Open your current booking table and look for any place where "is this slot free?" is answered by a SELECT before an INSERT. Add btree_gist, convert the start and end columns into a tstzrange, and create the bookings_no_overlap exclusion constraint in a migration. If the migration fails, PostgreSQL has just shown you the double bookings that already exist in your data. Clean those up first, then map 23P01 to a 409 in your API.