Unique Index with multiple columns

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!

1 Like

Try using psql and \d the table to get the actual name of the index.

Using Postico I can see it’s downloads_daily_unique_request_index.

For the latter form, does it help if you use fragment("accessed_at::date")? The error message suggests that Ecto is wrapping the name in double-quotes…

I get

undefined function fragment/1

I think it’s only usable inside a from clause? But your hint points me in the right direction. This plain sql works:

INSERT INTO "downloads" ("accessed_at","file_id","request_id","inserted_at","updated_at") 
VALUES (NOW(),1,'fooxyz',NOW(),NOW()) 
ON CONFLICT ("file_id","request_id", CAST(accessed_at AS DATE)) DO NOTHING RETURNING "id"

… but I’m not sure I can generate this in Ecto if fragments don’t work there :thinking:

Dunno if it works outside of Ecto.Query.from, but you might try adding import Ecto.Query to make fragment visible.

FWIW, the Postgres docs mention that the conflict target can be omitted for ON CONFLICT ... DO NOTHING cases.

3 Likes

:man_facepalming: Of course! All I care about is that no duplicates are inserted and that’s ensured by the index itself. Thanks!

1 Like