How to UPSERT with clojure.java.jdbc

user_7423908 0
Viewed 543

I use insert-multi! in clojure.java.jdbc to insert multiple rows into a Postgresql table. For some reason, I get duplicate primary keys in the rows to be inserted. These have to be ignored and the rest have to be inserted normally. So I need to "upsert" with the ON CONFLICT DO NOTHING syntax. This is how to use clojure.java.jdbc:

(jdbc/insert-multi! db-spec :fruit
                [:name :cost]
                [["Pomegranate" 585] 
                 ["Kiwifruit" 93]])

but I actually have to do the below:

INSERT INTO fruit (name, cost)
VALUES ("Pomegranate", 585), 
       ("Kiwifruit", 93) 
ON CONFLICT (name) DO NOTHING;

Would there be any workaround for this? Should I just prepare insert queries in strings on my own?

jdbc upsert conflict clojure postgresql
2 Answers
user_37430910

You can use HoneySQL with HoneySQL PostgreSQL helpers to use upsert (ON CONFLICT DO ...):

(require '[honeysql-postgres.helpers :as sql.helpers])

(-> (insert-into :fruit)
    (values [{:cost 585 :name "Pomegrenade"}
             {:cost 93 :name "Kiwifruit"}])
    (sql.helpers/upsert (-> (sql.helpers/on-conflict :name)
                            (sql.helpers/do-nothing)))
    (sql.helpers/returning :*)
    sql.helpers/format)
=> ["INSERT INTO distributors (cost, name) VALUES (?, ?), (?, ?) ON CONFLICT (name) DO NOTHING RETURNING *" 
    585 "Pomegrenade" 
    93 "Kiwifruit"]
user_26099800
Related