Ecto.Repo.insert! with “on_conflict: :nothing” raising a Postgresql “syntax error at or near ”ON“”

Question

I'm totally new to Elixir and Phoenix, and am currently working through the book "Programming Phoenix."

I've reached chapter 7 and I have a database table called categories with a column name and a DB-level uniqueness constraint on name.

If I run the following line when a category called "Hello" already exists, I get an Ecto.ConstraintError as expected:

> Rumbl.Repo.insert!(%Rumbl.Multimedia.Category{name: "Hello"})
[debug] QUERY ERROR db=3.2ms queue=4.9ms idle=9982.9ms
INSERT INTO "categories" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id" ["Hello", ~N[2020-04-18 07:05:04], ~N[2020-04-18 07:05:04]]
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * categories_name_index (unique_constraint)

Now, the book tells me that I can add the option on_conflict: :nothing to my call to insert! and it will prevent an error from being raised. But what actually happens is I get a postgres syntax error:

> Rumbl.Repo.insert!(%Rumbl.Multimedia.Category{name: "Hello"}, on_conflict: :nothing)
** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "ON"

    query: INSERT INTO "categories" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) ON CONFLICT DO NOTHING RETURNING "id"
    (ecto_sql) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto) lib/ecto/repo/schema.ex:657: Ecto.Repo.Schema.apply/4
    (ecto) lib/ecto/repo/schema.ex:263: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto) lib/ecto/repo/schema.ex:164: Ecto.Repo.Schema.insert!/4
[debug] QUERY ERROR db=0.0ms queue=0.8ms idle=9038.9ms
INSERT INTO "categories" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) ON CONFLICT DO NOTHING RETURNING "id" ["Hello", ~N[2020-04-18 07:05:13], ~N[2020-04-18 07:05:13]]

Version numbers:

Elixir 1.9.4
Phoenix 1.4.10
Ecto 3.4.2
Postgres 12.1

GMA GMA · Accepted Answer · 2020-04-18T07:40:44

Turns out I was mistaken that my postgres version is 12.1, this is actually the installed version of my psql client and not my postgres server, which was running 9.4. ON CONFLICT was added in 9.5, so the solution is to upgrade my postgres server.

Ecto.Repo.insert! with “on_conflict: :nothing” raising a Postgresql “syntax error at or near ”ON“”

1 Answers