I’ve been building a booking system for an inn recently, and one of the first things I had to think through was how to model dates. A booking can span a single night or several months, and you need to store, query, and constrain those ranges reliably. This is the same problem that Booking.com, Airbnb, and every other accommodation platform has to solve.

My first instinct was two columns — check_in and check_out. It’s what every tutorial shows. But we ran into enough off-by-one bugs and overlap edge cases that I started looking for something better. It turns out Postgres has something native that’s perfect for this: range types. Here’s what it looks like in practice and some things I’ve learned working with it.

The problem with separate columns

The typical approach is to store check_in and check_out as two date columns. Checking for overlaps then requires something like:

SELECT * FROM bookings
WHERE room_id = 1
  AND check_in < '2026-03-15'
  AND check_out > '2026-03-10';

This works, but it gets unwieldy fast. Every query that cares about date overlap has to repeat this two-sided comparison. Off-by-one errors creep in depending on whether your bounds are inclusive or exclusive, and different parts of the codebase inevitably make different assumptions.

The bigger problem is that there’s no way to express “these ranges must not overlap” as a database constraint with two separate columns. That means every place you insert or update bookings, you need to remember to include overlap checks yourself. Nothing in the schema tells you those checks need to happen. A new developer, a migration script, or a quick console fix can easily insert a duplicate booking without realising. The database won’t stop you.

PostgreSQL range types

Postgres has a native daterange type that stores a range of dates as a single value. Under the hood it’s stored as a compact binary representation, but you interact with it using a bracket syntax:

[2026-03-10, 2026-03-15)  -- occupies nights of Mar 10-14, checkout on Mar 15

Square bracket means inclusive (this date is part of the range), round bracket means exclusive (this date is not). Because Postgres understands this natively, it can do powerful things with it: overlap checks, containment tests, intersections, and adjacency comparisons are all built-in operators handled at the database layer.

Instead of two columns, store the booking as a single daterange. The convention that makes everything work: use a half-open range where the start is inclusive and the end is exclusive.

This seemingly small decision eliminates a whole class of bugs:

  • Date.diff(to, from) gives you exactly the number of nights, no off-by-one
  • Adjacent bookings naturally don’t conflict: [Mar 10, Mar 14) and [Mar 14, Mar 18) touch but don’t overlap
  • That touching point is the transition day, where one guest checks out and the next checks in

Overlap checks collapse to a single operator, &&:

SELECT * FROM bookings
WHERE room_id = 1
  AND date_range && '[2026-03-10, 2026-03-15)'::daterange;

One column, one operator, no ambiguity about bounds.

The exclusion constraint

With a range column in place, Postgres can enforce non-overlapping bookings at the database level using a GiST exclusion constraint:

ALTER TABLE bookings
ADD CONSTRAINT bookings_no_overlap
EXCLUDE USING gist (
  room_id WITH =,
  date_range WITH &&
);

This says: for any two rows where room_id is equal and date_range overlaps, reject the insert. No application code, no race conditions.

But what about cancellations? If a booking gets cancelled, you want those dates freed up immediately. A partial constraint handles this:

EXCLUDE USING gist (
  room_id WITH =,
  date_range WITH &&
)
WHERE (status <> 'cancelled');

Cancelled bookings are excluded from the constraint entirely, so those dates become available for rebooking without any cleanup.

In Ecto, register the constraint on the changeset so it gets caught gracefully instead of raising:

changeset
|> exclusion_constraint(:date_range, name: "bookings_no_overlap")

This turns a database error into a changeset error that can be surfaced to the user.

Custom Ecto type

Working with Postgres ranges in Elixir requires a custom Ecto type that bridges between representations. The type handles three formats through cast/1:

  • Maps from application code: %{from: ~D[2026-03-10], to: ~D[2026-03-15]}
  • Postgrex ranges from the database: %Postgrex.Range{lower: ..., upper: ...}
  • Raw strings from forms or API params: "[2026-03-10,2026-03-15)"

All three funnel through one type, so the rest of the codebase just works with %{from, to} maps without caring where the data came from. Ecto custom types are one of those things that make Postgres + Elixir such a strong pairing — you can extend the type system to match your domain without fighting the ORM or dropping down to raw SQL.

The critical detail when dumping to the database is upper_inclusive: false, enforcing the half-open convention at the boundary:

def dump(%{from: %Date{} = from, to: %Date{} = to}) do
  range = %Postgrex.Range{
    lower: from,
    upper: to,
    lower_inclusive: true,
    upper_inclusive: false
  }
  {:ok, range}
end

Checking availability

With the range type in place, availability checks become a simple overlap query:

def check_availability(room_id, %Date{} = start_date, %Date{} = end_date) do
  query_range = %Postgrex.Range{
    lower: start_date,
    upper: end_date,
    lower_inclusive: true,
    upper_inclusive: false
  }

  query = from(b in Booking,
    where: b.room_id == ^room_id,
    where: fragment("? && ?", b.date_range, ^query_range),
    select: b.id)

  if Repo.exists?(query), do: {:error, :date_range_conflict}, else: :ok
end

Transition days

The half-open convention pays off when building a calendar UI. Because the checkout date falls outside the range, it’s naturally available as a check-in for the next guest. Without any special-case date arithmetic, the availability logic can classify each date into one of four states:

  • blocked - falls inside an existing booking, unavailable
  • checkin only - it’s another guest’s checkout date, so a new booking can start here but not end here
  • checkout only - it’s another guest’s checkin date, so a booking can only end here
  • transition day - both a checkout and a checkin for different bookings, the turnover day

These states fall directly out of the data model. You query the ranges, check which ones touch each date, and the classification is just set logic on the results. No date arithmetic, no “subtract one day to find the last occupied night” type of reasoning.

Indexing for performance

A GiST index keeps overlap queries fast:

create index(:bookings, [:room_id, :date_range],
  using: :gist,
  name: :bookings_room_date_range_gist_idx)

A reusable pattern

The same daterange column, custom Ecto type, and GiST index pattern applies beyond just bookings. In this system, three different tables use it:

  • bookings - when is this room occupied?
  • add-ons - when does this extra service (cleaning, breakfast) apply?
  • availability overrides - when is this room blocked off or repriced?

All three share the same DateRangeType Ecto module, the same && overlap queries, and the same GiST indexes. Once you have the custom type working, adding it to a new table is just a field declaration.

For booking systems, range types and exclusion constraints handle storage, querying, overlap prevention, and calendar logic from one native type. The same pattern works anywhere you have ranges that shouldn’t overlap: scheduling systems, resource allocation, event planning, employee shift management, even IP address range assignments.

Trade-offs

A few things worth being honest about:

  • Postgres-specific. daterange and EXCLUDE USING gist are Postgres features — if you ever need to support MySQL or SQLite, this won’t port. For us that’s fine; we’re committed to Postgres. But it’s worth knowing upfront.
  • GiST indexes are slower than B-tree for simple lookups. The exclusion constraint uses a GiST index, which is optimised for overlap and containment checks but slower than a B-tree for exact match or range scans. For a booking system with thousands of rows this is irrelevant, but at millions of bookings per table you’d want to benchmark.
  • The custom Ecto type has a learning curve. Debugging range issues means understanding Postgrex’s %Range{} struct, the bracket/paren syntax, and the half-open convention. The first time a colleague hit a casting error, it took a while to figure out which of the three cast/1 clauses was involved. Once you understand it the type is solid, but there’s an initial hump.
  • Exclusion constraint errors aren’t the friendliest. The raw Postgres error message is something like "conflicting key value violates exclusion constraint" — not great for surfacing to end users. The exclusion_constraint/3 in Ecto helps, but you still need to map it to a user-friendly message yourself.

What I like most is the peace of mind. Once the constraint is in place at the database level, you know your data is correct regardless of how it gets there. You stop worrying about edge cases in overlap logic and just build features.