PostgreSQL Unique Constraint: Prevent Double Booking

A PostgreSQL unique constraint prevents double booking by rejecting any new row that duplicates a value already present in a constrained column or set of columns. When you apply a unique constraint to a column like room_id combined with a booking_date, the database automatically checks every insert and update, and if a row with the same combination already exists, the operation fails with an error—so two bookings for the same resource at the same time are impossible at the database level.

What a Unique Constraint Actually Does

A unique constraint is a database rule that guarantees every value in a column (or combination of columns) is distinct across all rows in a table. It is implemented as a unique index, meaning the database maintains a sorted structure to quickly look up existing values. When you try to insert a row that would create a duplicate, PostgreSQL aborts the operation and returns an error like duplicate key value violates unique constraint. This check is atomic: it happens as part of the transaction, so two concurrent requests cannot both slip through.

For booking systems, the constraint is usually placed on the natural key that defines a booking slot—for example, room_id and start_time. Once the constraint exists, the database itself enforces the rule, regardless of how many application servers are running or how fast they are. This is a fundamental advantage over application-level checks, which can fail due to race conditions.

How It Prevents Double Booking: The Mechanics

When you define a unique constraint on (room_id, start_time), PostgreSQL creates a b-tree index on those columns. Every insert checks the index for an existing entry with the same values. If found, the insert is rejected. The same applies to updates: changing a row’s start_time to a value that already exists for that room will fail.

The critical part is concurrency. In a multi-user system, two users might try to book the same room at the same time. Without a constraint, both requests could read the table, see no conflict, and both insert—a classic race condition. With a unique constraint, the first insert succeeds and locks the index entry. The second insert blocks until the first transaction commits or rolls back. If the first commits, the second gets a duplicate key error; if the first rolls back, the second proceeds. This serialization is handled automatically by PostgreSQL’s transaction system.

Adding a Unique Constraint to Your Table

You can add a unique constraint either when creating a table or later with an ALTER TABLE statement. The syntax is straightforward:

CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    start_time TIMESTAMPTZ NOT NULL,
    end_time TIMESTAMPTZ NOT NULL,
    CONSTRAINT no_double_booking UNIQUE (room_id, start_time)
);

If the table already exists, you can add the constraint with:

ALTER TABLE bookings ADD CONSTRAINT no_double_booking UNIQUE (room_id, start_time);

Before adding a constraint to an existing table, ensure there are no duplicate rows already; otherwise the command will fail. You can find duplicates with a GROUP BY query and clean them up first.

Choosing the Right Columns for the Constraint

The key is to choose columns that uniquely identify a booking slot. For a meeting room, that might be room_id and start_time. For a hotel, it could be room_id and check_in_date if you only allow one booking per night. For a resource like a car, it might be car_id and pickup_time.

Consider what “double booking” means in your domain. If bookings can overlap in time but not start at the same moment, a unique constraint on start time alone is not enough—you would also need to prevent overlapping intervals. A unique constraint only catches exact duplicates, not overlapping ranges. For overlapping intervals, you need an exclusion constraint (see below).

Common Pitfalls and How to Avoid Them

• NULL values are not considered equal. In PostgreSQL, NULLs are distinct by default, so a unique constraint on a nullable column will allow multiple rows with NULL. If your booking system treats NULL as “not specified,” you may need to use a COALESCE expression or a partial unique index.
• Time zones and timestamps. If you store start_time as TIMESTAMP without time zone, two bookings at the same wall-clock time in different time zones will be considered duplicates. Always use TIMESTAMPTZ for absolute times.
• Precision of time. If you store times with millisecond precision, two bookings at 10:00:00.000 and 10:00:00.001 are not duplicates. If you want to treat them as the same slot, round to the desired precision before inserting.
• Updates that create duplicates. A unique constraint also applies to updates. If you change a booking’s time to one that already exists, the update will fail. Make sure your application handles this error gracefully.

Alternatives and Extensions: Exclusion Constraints

For many booking scenarios, a unique constraint is not enough because you need to prevent overlapping time ranges, not just identical start times. PostgreSQL offers exclusion constraints for this purpose. An exclusion constraint uses a special index type (GiST) and allows you to define a condition that must not be true for any two rows. For example:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    start_time TIMESTAMPTZ NOT NULL,
    end_time TIMESTAMPTZ NOT NULL,
    EXCLUDE USING gist (room_id WITH =, tstzrange(start_time, end_time) WITH &&)
);

This ensures that no two rows for the same room have overlapping time ranges. It is a more powerful tool than a unique constraint, but it is also more complex and requires the btree_gist extension to combine equality and range operators.

Performance and Cost Considerations

A unique constraint adds a unique index, which speeds up lookups on those columns but adds overhead to every insert and update because the index must be maintained. For high-write workloads, this can be a bottleneck. However, the cost is usually acceptable for booking systems where correctness is critical. You can mitigate the overhead by keeping the index size small (fewer columns) and by using appropriate data types (e.g., INT instead of VARCHAR).

In terms of concurrency, the unique index can cause lock contention if many transactions try to insert the same key simultaneously. PostgreSQL handles this by blocking the second transaction until the first commits. This is usually fine, but if you expect extremely high contention on a single slot, you might need to design your application to handle the resulting wait times or use advisory locks for more complex logic.

Frequently Asked Questions

Can a unique constraint be bypassed by a race condition?

No. PostgreSQL’s unique constraint is enforced atomically at the database level. Even if two transactions attempt to insert the same key at the same time, the database ensures only one succeeds. The second transaction will wait for the first to commit or roll back, and then it will fail with a duplicate key error if the first committed.

What if I need to allow multiple bookings per day but not at the same time?

Use a unique constraint on (room_id, start_time) if you only care about the start time. If you need to prevent overlapping intervals, use an exclusion constraint with a range type, as shown above.

How do I handle NULL values in a unique constraint?

By default, NULL values are considered distinct, so multiple rows with NULL are allowed. To treat NULL as a specific value, you can use a partial unique index like CREATE UNIQUE INDEX ON bookings (room_id, start_time) WHERE start_time IS NOT NULL; or use COALESCE in a functional index.

Does a unique constraint work with partitioned tables?

Yes, you can add a unique constraint to a partitioned table, but the constraint must include the partition key. For example, if you partition by room_id, the unique constraint on (room_id, start_time) is allowed because it includes the partition key. If the constraint does not include the partition key, PostgreSQL will reject it.

What is the difference between a unique constraint and a unique index?

Functionally, they are the same—a unique constraint is implemented as a unique index. The difference is semantic: a constraint is a logical rule, while an index is a physical structure. You can create a unique index directly without a constraint, but it will not appear in the information_schema as a constraint. For most purposes, use a constraint because it is clearer and easier to manage.

Can I add a unique constraint to an existing table with duplicate data?

No, the ALTER TABLE command will fail if duplicates exist. You must first identify and remove or merge duplicate rows. Use a query like SELECT room_id, start_time, COUNT(*) FROM bookings GROUP BY 1,2 HAVING COUNT(*) > 1; to find them.

Related

• Freeware OCR Program: Best Free Options
• Highlight Bottom Values: How to Make Them Stand Out
• Bentley Inspired Wireless Speaker Headphones