Fork me on GitHub

Sorry if I'm missing it in the docs, but could someone suggest how I'd do a bulk update with Postgres? Given a set of rows like [{:id 1 :x "foo" :y nil} {:id 2 :x "bar" :y nil}], I want to update the value of the :y by merging a vector of maps that I have in Clojure, like [{:id 1 :y 100} {:id 2, :y 200}], so the resulting rows would be [{:id 1 :x "foo" :y 100} {:id 2 :x "bar" :y 200}].


I think I got it:

(execute! db
          (-> (hh/create-table :test)
                [[:did :int]
                 [:x :varchar]
                 [:y :int]
                 [:z :int]
                 [[:primary-key :did :x]]])
(execute! db
          (-> (hh/insert-into :test)
              (hh/values [{:did 1 :x "foo"}
                          {:did 2 :x "bar"}])
              (sql/format {:pretty true})))
(execute! db
          (-> (hh/insert-into :test)
              (hh/values [{:did 1 :x "foo" :y 100}
                          {:did 2 :x "bar" :y 200}])
              (hh/on-conflict (hh/on-constraint :test_pkey))
              (hh/do-update-set :y)
(execute! db
          (-> (hh/insert-into :test)
              (hh/values [{:did 1 :x "foo" :z 888}
                          {:did 2 :x "bar" :z 999}])
              (hh/on-conflict (hh/on-constraint :test_pkey))
              (hh/do-update-set :z)
Please suggest if there is a better way. Also curious if there is a way to skip the operation if a matching PK does not exist.


I ultimately arrived at using Postgres CTE:

(-> (hh/with [[:updates {:columns [:did :y]}] 
              {:values [[1 100]               
                        [2 200]]}])           
    (hh/update :test)                         
    (hh/set {:y :updates/y})                  
    (hh/from :updates)                        
    (hh/where := :test/did :updates/did)