I have a table called bookings where it has the start_datetime, end_datetime, meeting_room_id and status ('confirmed', 'pending', 'cancelled')
So in my migration, I added a unique partial index for start_datetime, end_datetime, meeting_room_id and status where status is unique only for confirmed and pending
Below is my migration file for creating the unique partial index
defmodule MyApp.Repo.Migrations.AddBookingsUniqueIndex do
use Ecto.Migration
def change do
create unique_index(:bookings, [:start_datetime, :end_datetime, :meeting_room_id, :status], where: "STATUS = 'confirmed' OR STATUS = 'pending'")
end
end
I’m not sure if I do it correctly or not. Please do correct me if there is anything wrong.
I tested by running multiple HTTP requests at the same time and only one request with status: confirmed or pending can be created.
But other requests will return response code 500 which is not a good way to handle the response.
I know I can add the unique_constraint([:start_datetime, :end_datetime, :meeting_room_id, :status]) but other status such as cancelled can have multiple.
How do I create add the unique_constraint in bookings schema to handle the where it met my migration requirements?
If you have any question, don’t be shy to ask. I’ll try to answer your question as best as I can.
Any help would be great! Thanks in advance!
I believe that in this case the unique constraint is the way to go. The code that does the insertion will receive an error from postgres indicating the index that had a unique violation. It looks at the unitque constraints defined on the changeset and returns it up as a failed changeset, rather than a hard error. This makes it easy to then return a 422 status instead of the 500.
The key here is that the unique constraint doesn’t do anything on its own. The database has to return the violation on insertion or update.
If you ever have multiple partial on the same columns, you will need to specify the name of the index in both the creation and the changeset constraint.
Adding one more option to this. There is a functionality in postgres where you can set ranges, and add a constraint to exclude records. https://www.postgresql.org/docs/9.3/rangetypes.html
To support this from ecto , you need to go with ecto behavior,
I have used it in integer types. They do support date time type as well. Please do read it. If any doubts, i am happy to help you out.
This way you dont need two columns, and its queryable as well.
Below constraint will raise postgres exception, when there is an overlap. We can use ecto custom validation and return proper errors to the user.
constraint(:table_name, :no_overlapping_column_age,
exclude: ~S|gist (column_name WITH &&)|)
