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!