Clojurians Log v2

Hi

I am 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

But this returns:

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

Any idea how to fix?

Try putting prefixing foo with a colon.

Okay

Thanks

That results in

clojure.lang.ExceptionInfo: Parameter Mismatch: :foo parameter data not found.

Added question to SO too: https://stackoverflow.com/questions/56581792/using-hugsql-to-insert-multiple-rows-at-once-into-postgresql-table-with-on-confl

You have to say what :foo is, so it has to be a key in the map you provide when you call add-things.

You have a key for :values, but also need one for :foo.

You should also make sure the SQL you want to generate is valid. Isn't SET normally used with an UPDATE statement?

Oh I see - it is an insert statement that falls back to an update statement. I think your question got answered in SO.

Thanks for the help

it works by using foo = EXCLUDED.foo 🙂