1

I have a table in a PostgreSQL DB like this to represent a booking system for office desks. Employees can book one desk (seat_id) per day.

ID Booked_Date Seat_ID Employee_ID Status
1 2022-07-08 10C id1 booked
2 2022-07-08 20C id2 cancelled

How to make a unique constraint with multiple column (booked_date, seat_id, and status with value 'booked') to guarantee that no more than one employee can book the same seat for the same day?

Note: employees can cancel the seat that they have booked and the row stays in the table with changed status.

Akhmad Zaki
  • 419
  • 7
  • 23

1 Answers1

2

Use a partial UNIQUE index:

CREATE UNIQUE INDEX ON booking_tbl (booked_date, seat_id)
WHERE status = 'booked';

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228