Use an index expression in conflict_target option for an insert

Hi,

for PostgreSQL (9.6), is it possible to use an index_expression for an insert (Repo.insert) as conflict_target option?

Because if I’m not mistaken, Ecto (?) expects a column name and quote it.

changeset = Foo.changeset(%Foo{...}, foo_params)
case changeset |> Repo.insert(on_conflict: [set: [name: changeset.changes.name]], conflict_target: "lower(name)") do

So it doesn’t work:

ERROR 42703 (undefined_column): column “LOWER(name)” does not exist

(same for conflict_target: :"LOWER(name)") Reading mix phoenix.server's output, I can read ON CONFLICT ("LOWER(name)"), I guess the quotes changes the meaning from expression to identifier and, sadly, PostgreSQL doesn’t allow the use of index’s name in CONFLICT clause.

How should I procede to use an index_expression (if it is possible)?

PS: I can’t use citext

Thanks.

2 Likes

This would be cool if it works.