Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2022-06-12

Channels

#honeysql
<
2022-06-12
>
sheluchin18:06:13

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}].

sheluchin15:06:50

I think I got it: 

(execute! db
          (-> (hh/create-table :test)
              (hh/with-columns
                [[:did :int]
                 [:x :varchar]
                 [:y :int]
                 [:z :int]
                 [[:primary-key :did :x]]])
              (sql/format)))
(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)
              (sql/format)))
(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)
              (sql/format)))
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.

sheluchin14:06:34

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)      
    (sql/format))))