Fork me on GitHub
#honeysql
<
2023-03-23
>
Daniel Stephens15:03:02

Hi all, does anyone know how to use postgres' on-conflict stuff where the update should increment a value from honeysql. Something like:

(honey/format
  {:insert-into   :table
   :values        [{:id "id" :counter 1}]
   :on-conflict   [:id]
   :do-update-set [:+ :counter 1]})
this is made up, but results in:
["INSERT INTO table (id, counter) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET + = EXCLUDED.+, counter = EXCLUDED.counter, ? = EXCLUDED.?"
 "id"
 1
 1
 1]
whereas I want something like:
["INSERT INTO table (id, counter)
   VALUES (?, ?)
   ON CONFLICT (id)
   DO UPDATE SET
   counter = table.counter + ?"
  "id"
  1
  1]
Probably a bit specific or unusual as a case, but just in case!

seancorfield15:03:50

You want the hash map form of :do-update-set:

user=> (sql/format {:insert-into :table :values [{:id "id" :counter 1}] :on-conflict [:id] :do-update-set {:counter [:+ :counter 1]}})
["INSERT INTO table (id, counter) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET counter = counter + ?" "id" 1 1]
user=>

seancorfield15:03:49

If you specifically need table.counter:

user=> (sql/format {:insert-into :table :values [{:id "id" :counter 1}] :on-conflict [:id] :do-update-set {:counter [:+ :table.counter 1]}})
["INSERT INTO table (id, counter) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET counter = table.counter + ?" "id" 1 1]
user=>

seancorfield16:03:30

The vector form of :do-update-set only sets excluded columns.

Daniel Stephens16:03:06

ha, amazing, thanks @U04V70XH6 that was speedy! I appreciate the help, I did look through some docs FWIW and didn't notice this option https://cljdoc.org/d/seancorfield/honeysql/2.0.0-rc2/doc/getting-started/postgresql-support#upsert

seancorfield16:03:04

Can you create an issue on GH for me to make that clearer? I suspect there's a test showing it, but it really should be in the docs too... Thanks.

🙏 2