I have a table called
bookings where it has the
status ('confirmed', 'pending', 'cancelled')
So in my migration, I added a unique partial index for
status is unique only for
Below is my migration file for creating the unique partial index
defmodule MyApp.Repo.Migrations.AddBookingsUniqueIndex do
def change do
create unique_index(:bookings, [:start_datetime, :end_datetime, :meeting_room_id, :status], where: "STATUS = 'confirmed' OR STATUS = 'pending'")
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
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.
Thanks for the information. Tested all worked out as you said
Adding one more option to this. There is a functionality in postgres where you can set ranges, and add a constraint to exclude records.
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.
exclude: ~S|gist (column_name WITH &&)|)
Awesome. Thanks for the information. Will look through it. Might be useful in my case. Thanks again!