PostgreSQL 18 gave me back 30 lines of code

While working on a side project, a scheduling tool, I hit a rule that turned out to be deceptively hard to enforce in SQL: two appointments for the same practitioner cannot overlap in time, unless the user explicitly marks one as an overbook.

PostgreSQL 18 introduced WITHOUT OVERLAPS on unique constraints and PERIOD on foreign keys. This change moved two important business rules from application code into the database schema.

Here’s what changed in our TypeScript booking endpoint when we upgraded from PG 17 to PG 18.

How we did it in PG 17

We had a complex EXCLUDE USING GIST constraint:

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE appointments
    ADD CONSTRAINT appointments_no_overlap
    EXCLUDE USING GIST (
        practitioner_id WITH =,
        tstzrange(start_time, end_time, '[)') WITH &&
    )
    WHERE (
        status NOT IN ('cancelled', 'no_show')
        AND parent_appointment_id IS NULL
        AND is_overbook = FALSE
    );

It worked, but it came with real pain:

• Required the btree_gist extension
• Used a strange error code (23P01)
• Needed a manual backfill during migration
• Was hard to read and maintain
• The second rule (“appointment must be inside the practitioner’s availability”) had no database enforcement at all

How it looks in PG 18

-- 1. Practitioner availability windows
CREATE TABLE availability (
    practitioner_id UUID NOT NULL,
    valid_during    tstzrange NOT NULL,
    PRIMARY KEY (practitioner_id, valid_during WITHOUT OVERLAPS)
);

-- 2. Add a generated range column
ALTER TABLE appointments
    ADD COLUMN during tstzrange
        GENERATED ALWAYS AS (tstzrange(start_time, end_time, '[)')) STORED;

-- 3. Overlap constraint (only for normal appointments)
ALTER TABLE appointments
    ADD CONSTRAINT appointments_no_overlap
    UNIQUE (practitioner_id, during WITHOUT OVERLAPS)
        WHERE (
            status NOT IN ('cancelled', 'no_show')
            AND parent_appointment_id IS NULL
            AND is_overbook = FALSE
        );

-- 4. Temporal foreign key: appointment must fall inside availability
ALTER TABLE appointments
    ADD CONSTRAINT appointments_inside_availability
    FOREIGN KEY (practitioner_id, PERIOD during)
        REFERENCES availability (practitioner_id, PERIOD valid_during);

No extension. No GiST. No weird error codes.

The booking handler (TypeScript)

Before (PG 17) — ~40 lines

We needed three queries inside a SERIALIZABLE transaction, plus custom error handling for the strange 23P01 code.

After (PG 18) — ~12 lines

export async function bookAppointment(req: BookingRequest): Promise<void> {
  try {
    await sql`
      INSERT INTO appointments (id, practitioner_id, start_time, end_time, is_overbook)
      VALUES (
        ${req.id},
        ${req.practitionerId},
        ${req.startTime},
        ${req.endTime},
        ${req.isOverbook}
      )
    `;
  } catch (e: any) {
    if (e.code === "23505") throw new SlotTakenError();      // overlap
    if (e.code === "23503") throw new OutsideAvailabilityError(); // outside availability
    throw e;
  }
}

One single INSERT. No transaction. No race conditions. Clean error codes.

What changed

What it doesn’t fix

• You still need a range column (during). We kept start_time and end_time for the rest of the app and added a generated column. It costs a bit more storage.
• Temporal foreign keys don’t support ON DELETE CASCADE or RESTRICT. Deleting an availability window still requires application-level cleanup.
• UX messages still need translation. foreign_key_violation is not the same as “the doctor isn’t working at that time.”
• Partial WHERE predicates with WITHOUT OVERLAPS should be tested carefully before going to production.

Bottom line

PostgreSQL 18 didn’t add new power — it added clear syntax.

We moved two business rules out of our application code and into the database where they belong. The booking handler went from a complex transactional function to a simple 12-line insert.

For a rule that used to live in a 50-line migration comment with workarounds and custom error handling, this is a real improvement.