This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2022-06-12
Channels
- # ai (1)
- # aleph (11)
- # announcements (9)
- # aws (1)
- # beginners (61)
- # chlorine-clover (2)
- # clj-kondo (1)
- # clojure (35)
- # clojure-australia (1)
- # clojure-china (1)
- # clojure-europe (1)
- # clojure-filipino (1)
- # clojure-france (2)
- # clojure-hk (1)
- # clojure-indonesia (1)
- # clojure-japan (1)
- # clojure-korea (1)
- # clojure-my (1)
- # clojure-sg (1)
- # clojure-taiwan (1)
- # clojure-uk (1)
- # clojured (14)
- # clojurescript (45)
- # cursive (8)
- # data-science (2)
- # events (1)
- # fulcro (2)
- # gratitude (4)
- # helix (1)
- # honeysql (3)
- # introduce-yourself (1)
- # malli (4)
- # minecraft (4)
- # nbb (23)
- # off-topic (57)
- # polylith (4)
- # reagent (2)
- # sci (23)
- # shadow-cljs (7)
- # vim (1)
- # xtdb (17)
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)
(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.