I have a unqiue index over three columns where one is computed ([:file_id, :request_id, "(accessed_at::date)"]) and I can’t figure out how to use :conflict_target on insert correctly.

The Ecto docs made me understand that a unique_index creates a constraint so I could use conflict_target: {:constraint, :downloads_daily_unique_request_index} but that doesn’t seem to be the case.

Here’s my abbreviated setup:

# Migration
defmodule DemoApp.Repo.Migrations.CreateDownloads do
  use Ecto.Migration

  def change do
    create table(:downloads) do
      add :request_id, :string
      add :accessed_at, :utc_datetime
      add :file_id, references(:audio_files, on_delete: :nothing)

      timestamps()
    end

    create unique_index(
             :downloads,
             [:file_id, :request_id, "(accessed_at::date)"],
             name: :downloads_daily_unique_request_index
           )
  end
end

# Schema
defmodule DemoApp.Tracking.Download do
  use Ecto.Schema

  # ...

  schema "downloads" do
    field :request_id, :string
    field :accessed_at, :utc_datetime

    belongs_to :file, AudioFile

    timestamps()
  end

  @doc false
  def changeset(download, attrs) do
    download
    |> cast(attrs, [
      :request_id,
      :accessed_at
    ])
    |> validate_required([
      :request_id,
      :accessed_at
    ])
    |> unique_constraint(
      :request_id,
      name: :downloads_daily_unique_request_index
    )
  end
end

There doesn’t seem to be a constraint setup in the database:

Download.changeset(%Download{}, %{request_id: "fooxyz", accessed_at: DateTime.utc_now()})
|> Ecto.Changeset.put_assoc(:file, file)
|> Repo.insert(
  on_conflict: :nothing,
  conflict_target: {:constraint, :downloads_daily_unique_request_index}
)

** (Postgrex.Error) ERROR 42704 (undefined_object) constraint “downloads_daily_unique_request_index” for table “downloads” does not exist
query: INSERT INTO “downloads” (“accessed_at”,“file_id”,“request_id”,“inserted_at”,“updated_at”) VALUES ($1,$2,$3,$4,$5) ON CONFLICT ON CONSTRAINT “downloads_daily_unique_request_index” DO NOTHING RETURNING “id”

Listing the columns does not work because one of them is calculated:

Download.changeset(%Download{}, %{request_id: "fooxyz", accessed_at: DateTime.utc_now()})
|> Ecto.Changeset.put_assoc(:file, file)
|> Repo.insert(
  on_conflict: :nothing,
  conflict_target: [:file_id, :request_id, "accessed_at::date"]
)

** (Postgrex.Error) ERROR 42703 (undefined_column) column “accessed_at::date” does not exist

What am I missing?

Thanks!