Unique Index with multiple columns

ericteubert

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!

6 comments

#ecto #databases #postgresql

1 4688 6

2019-08-30 08:07:56 UTC

Showing Posts 1 to 6

Show Best Posts
Show All (oldest first)
Show All (newest first)

danj

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

Post #1

ericteubert

Using Postico I can see it’s downloads_daily_unique_request_index.

Post #2

al2o3cr

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…

Post #3

ericteubert

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