Adventures in Ecto and PostgreSQL: Partial unique indexes and upserts with associations

Let’s say you were asked by your government to implement a national dog registry in order to help them to enforce their new one-dog-policy. Everyone who wants to buy a dog will need to register it at the municipal government.

Since your government cares a lot about the well-being of the nation’s dogs and generally invests a lot of time and resources into research endeavours, they want to collect some additional data on all the dogs and want all dog owners to check in once a year to keep the information up to date. Because a year is a long time between updates to conduct proper research and also to keep the feedback loop short regarding new dog-happiness policies, the government also plans to equip dogs and dog-related products with sensors in the future.


Versions: Elixir 1.10.0, ecto 3.3.3, ecto_sql 3.3.4, PostgreSQL 11.5

Let’s start off with this Ecto migration:

With this we create an owners table, a dogs table and a dog_toys table. A dog belongs to an owner and a dog toy belongs to a dog. The government will need a lot more information on the dogs, of course, including various psychological measures and health statistics, but let’s keep it simple for now.

We also create Ecto schemas for each of these tables, but since they are quite unremarkable, I’ll only give you the Dog schema:

Partial unique indexes

You decided to handle both dog registrations and updates to existing dogs with a single function. Don’t ask me why, you didn’t tell me.

This means we need to implement this as an upsert. The first thing we need is a unique index on the dogs table to prevent the clerk from inserting a second dog with the same owner. However, one-dog-policy does not mean that you may only have one single dog throughout your entire lifetime. Once your dog died, you can get a new one. This means the unique index may only apply if the dog is alive.

PostgreSQL allows you to define partial indexes to do just that.

In SQL that index would be defined like this:

Ecto.Migration.unique_index/3 allows you to add a where option, so the statement above looks like this in your application:

The first tests

We create a test module that uses the DataCase module Phoenix creates and sets up some fixtures. The complete code example is linked below. The first describe block only has a simple test to make sure you can insert a new dog into the database:

The params/2 function does nothing but add the ID of the second argument to the map that is passed as the first argument. I know that you know that there is a pipe operator.

This is the function that passes the tests:

The upsert test

Now on to the more interesting test case.

This looks similar as the previous test, but now you ensure that there is still only one dog in the database after calling the function a second time. This means if there is already a living dog for the owner in the database, the entry will be replaced silently. You slowly come to the conclusion that this behaviour is probably not what you would want in this scenario, but since you are already invested in this example, you decide to continue.

Unsurprisingly, this test fails with the current implementation. To perform an upsert, you can set the :on_conflict option when calling Repo.insert/2. When using PostgreSQL, you also need to set the :conflict_target option to tell PostgreSQL which constraints you expect. This can be a list of columns or the constraint name itself. Since we named the unique index, we can try this:

However, now we're getting this error in all the tests:

What’s going on?

Ecto turns the options above into this SQL fragment:

This is syntactically correct, but PostgreSQL requires you to specify the constraint including the whole WHERE clause. So we need to find a way to tell Ecto to do that. Unfortunately, there is no shortcut for this at the moment, but instead of passing the constraint name, Ecto also allows us to pass an unsafe SQL fragment.

This works!


Your plan was to update all the associations of the dog (except for the owner) with that same function call. So far, the test fixtures look like this:

Very simple, no associations. Let’s give Dwayne some toys and see what happens.

Running the tests again, we get a foreign key constraint error for the test create_or_update/1 replaces previous dog if alive:

The other tests pass, though. So inserting a dog including toys is no problem, but updating an existing dog with toys is.

Let’s remove the toys from the fixtures for a moment and update the test like this:

You will get an output similar to this:

As it happens, passing on_conflict: :replace_all to Repo.insert/2 will not only replace the values in the changeset, but also generate new values for all autogenerated fields including timestamps and the ID. If we want to keep some of the existing values, we can pass {:replace_all_except, fields} or {:replace, fields} as the option value. Let's change the on_conflict option of create_or_update/1 to:

We want to keep the original ID, but we also want to keep the inserted_at value, while we are happy to get an updated updated_at value.

If we run the test again, we will still see two different IDs. Why is that?

We configured our application with UUIDs as primary keys. Unlike auto-incrementing integer IDs, UUIDs are auto-generated by Ecto, not in the database (unless you set autogenerate to false in your schema and configure your database to generate them). And since Ecto already knows all values it generated or set itself, it doesn’t ask them from the database when running the query. So in our case, Ecto generates a UUID, sends an insert statement to the database, the database sees a conflict and makes a replacement instead, but keeps the old ID, and this existing ID is not returned back to Ecto.

The fix is easy, though. Repo.insert/2 accepts another option called :returning, which specifies which fields should be returned from the database. If you set returning to true, all fields will be returned.

So the final version of create_or_update/1 looks like this:

Now the IDs in the test output will match and the tests will also pass with fixtures including associations.

However, our tests still don't have any assertions about the associations. Let's change that by adding this test:

Similar to the previous test, except that we actually have a look at the dog toys after the upsert. @living_dog_1 had two toys, @living_dog_2 had only one, so we would expect to be only the single toy of dog 2 in the database after the update. But this test fails, the database includes both toys of dog 1 and the toy of dog 2.

The problem is that PostgreSQL can handle the replacement of the dog entry itself, but it doesn't know anything about the associations, and since we only passed an empty struct as the first argument to Ecto.Changeset.changeset/2 in our create_or_update/2 function, Ecto has no way of tracking the associations either.

Now, we could change that by 1) changing the function signature of create_or_update/2 and 2) passing a fully preloaded struct to the function, but that would defeat the purpose of the upsert function, because we could then as easily call a regular update function with that preloaded resource. Which means that if you want to do a complete replacement of the existing associations, you shouldn't use an upsert function after all.

Complete source code:

Don't look directly into the sun.