Clojurians Log v2

You could just use :EXCLUDED.name instead of that :raw expression:

user=> (->
{:insert-into [:user_preferences]
         :values [{:user_id 1
                   :name "sally"
                   :preferences [:lift {:theme "light"}]}]
         :on-conflict [:user_id]
         :do-update-set {:fields
                         {:name :EXCLUDED.name
                          :preferences
                          [:jsonb_strip_nulls
                           [:||
                            [:coalesce :user_preferences.preferences [:lift {}]]
                            [[:lift {:theme "light" :font nil}]]]]}}}
sql/format)
["INSERT INTO user_preferences (user_id, name, preferences) VALUES (?, ?, ?) ON CONFLICT (user_id) DO UPDATE SET name = EXCLUDED.name, preferences = JSONB_STRIP_NULLS(COALESCE(user_preferences.preferences, ?) || (?))" 1 "sally" {:theme "light"} {} {:theme "light", :font nil}]
user=>

(if you have quoting enabled, you might need :'EXCLUDED.name instead maybe)

i think i just figured it out. thanks just the same 😎

(deftest upsert-json-column-merge-strip-nulls
  (let [ds (db-fixtures/get-ds)]

    ; ✅ set up the test table
    (jdbc/execute!
      ds
      (hsql/format
        {:create-table :user_preferences
         :with-columns [[:user_id :int]
                        [:preferences :jsonb]
                        [[:primary-key :user_id]]]}))

    ; ✅ create a new user
    (jdbc/execute!
      ds
      (hsql/format
        {:insert-into [:user_preferences]
         :values [{:user_id 1 :preferences [:lift {:theme "dark"}]}]}))

    ; ✅ upsert the user's preferences
    (jdbc/execute!
      ds
      (hsql/format
        {:insert-into [:user_preferences]
         :values [{:user_id 1 :preferences [:lift {:theme "light"}]}]
         :on-conflict [:user_id]
         :do-update-set {:fields
                         {:preferences
                          [:jsonb_strip_nulls
                           [:|| [:coalesce :user_preferences.preferences [:lift {}]] [[:lift {:theme "light"}]]]]}}}))))

Yup, :lift is your friend with JSON object types in PostgreSQL.

yup. and thanks for the awesome library 😎. is there a way to combine :do-update-set with both :fields and the short term vector syntax for EXCLUDED?

(jdbc/execute!
  ds
  (hsql/format
    {:insert-into [:user_preferences]
     :values [{:user_id 1
               :name "sally"
               :preferences [:lift {:theme "light"}]}]
     :on-conflict [:user_id]
     :do-update-set {:fields
                     ; is there a way to get EXCLUDED for free when combined with :fields?
                     {:name "EXCLUDED.name"
                      :preferences [:jsonb_strip_nulls
                                    [:||
                                     [:coalesce :user_preferences.preferences [:lift {}]]
                                     [[:lift {:theme "light" :font nil}]]]]}}}))

actually, that didn’t work as expected

but the question still stands 🙂

this would be the correct version:

(jdbc/execute!
      ds
      (hsql/format
        {:insert-into [:user_preferences]
         :values [{:user_id 1
                   :name "sally"
                   :preferences [:lift {:theme "light"}]}]
         :on-conflict [:user_id]
         :do-update-set {:fields
                         {:name [:raw "EXCLUDED.name"]
                          :preferences
                          [:jsonb_strip_nulls
                           [:||
                            [:coalesce :user_preferences.preferences [:lift {}]]
                            [[:lift {:theme "light" :font nil}]]]]}}}))

I don't use PG so I can't give much guidance on specifics

no worries, [:raw "EXCLUDED.name"] works fine, thank you. on my side it’s a trip coming back from datalog to the relational world. like learning to ride a bike.. backwards 😉