Using HugSQL to INSERT multiple rows at once into PostgreSQL table with ON CONFLICT DO UPDATE

Question

I'm working with PostgreSQL and wanting to INSERT multiple rows at once with an ON CONFLICT DO UPDATE statement.

I've got something like this:

-- :name add-things! :! :n
INSERT INTO my_table (
  p,
  foo
)
VALUES :tuple*:values
ON CONFLICT (p) DO UPDATE
SET my_table.foo = foo

where p is the primary key.

I call this with:

(add-things! {:values [[1 1] [2 3]]})

But this returns: org.postgresql.util.PSQLException: ERROR: column reference "foo" is ambiguous.

Using SET my_table.foo = :foo (with a keyword parameter) leads to clojure.lang.ExceptionInfo: Parameter Mismatch: :foo parameter data not found, because there are no keyword parameters when using the :tuple*:values syntax.

Any idea how to accomplish this? Maybe by using Clojure code in the HugSQL query?

Answer 1

The problem here is the use of just foo inside the conflict resolution. There is a foo in the "insert data" and one on the actual table. You need to address the "insert data" somehow to resolve that conflict. The solution as stated in the docs is:

conflict_action specifies an alternative ON CONFLICT action. It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

So

...
SET foo = excluded.foo

solves the conflict.