Hi everyone! Short question: Is it possible to make insert-into set DEFAULT instead of NULL on missing keys? Usecase: I want to use the on-conflict and do-update-set of postgres with uuid primary keys. If I now had a list of mixed fields where some already have an id and some not, honeysql inserts a NULL for the primary key where its missing and will therefore not be able to insert the entry due to a missing primary key.

I believe I can get around it by assigning uuids generated by java.util.UUID/randomUUID for entries that don't hold an id already but I'd think it would be cleaner to let the database handle that

@alpox not sure I understand your question : honeysql just generates sql, it doesn't interact with the database at all - it doesn't actually insert anything.

Honeysql knows nothing about databases, nothing about columns or defaults.

@seancorfield Yes, my issue was with the sql generated by honeysql. If I take the data:

(def fields [{:id #uuid "e352239e-5ccc-4de4-8fcb-d9745a4c7739"
                :name "foo"
                :position 1
                :type "foobar"
                :table-field-name "foo"
                :entity-id (:entity/id new-entity)}

               {:name "bar"
                :position 2
                :type "bar"
                :table-field-name "bar"
                :entity-id (:entity/id new-entity)}])

(-> (hh/insert-into :field)
                     (hh/values fields) ; fields here
                     (hh/on-conflict :id)
                     (hh/do-update-set
                      :position
                      :type
                      :name
                      :table_field_name)
                     (hh/returning :id)
                     (hsql/format))

A very simple, quick way of moving on from this, is simply to do a string replacement (as what comes out of (hsql/format) is a string

(def sql "INSERT INTO field (id, name, position, type, table_field_name, entity_id) VALUES (?, ?, ?, ?, ?, ?), (NULL, ?, ?, ?, ?, ?)")

(clojure.string/replace sql #"\(NULL" "(DEFAULT")

"INSERT INTO field (id, name, position, type, table_field_name, entity_id) VALUES (?, ?, ?, ?, ?, ?), (DEFAULT, ?, ?, ?, ?, ?)"

Hmm yes thats a possible approach. But im not sure if this could not lead to followup errors in some cases where NULL is wanted still (e.g. explicitely specified nil)

I guess I have to apply it with caution

one thing at a time 🙂 Tackle that bridge if you come to it.

Thanks for the feedback :) ill go down that road for now as I dont think Ill run into trouble with this

In a lot of situations, you really do want NULL inserted in a column so the choice to use DEFAULT instead is extremely context-sensitive. I'm not sure what HoneySQL could actually do here to help.

You might even have a situation where you want NULL inserted for some nil columns (or missing columns), and DEFAULT for other columns in the same INSERT statement so how would HoneySQL know? There would have to be some way for you to tell it which columns should map to DEFAULT is nil/missing. That might be the best approach, on a per-call (to format) basis, as some sort of option with a set of column names?

https://github.com/seancorfield/honeysql/issues/366

> That might be the best approach, on a per-call (to `format`) basis, as some sort of option with a set of column names? Maybe something like:

(hsql/format sql {:insert-key-fallback {:id [:raw "DEFAULT"]}})

I'll have to give it sort thought, in terms of naming, and in terms of whether it should consider missing columns differently than nil values (in general HoneySQL doesn't -- and idiomatically Clojure generally doesn't). Not sure whether it needs to be a hash map from column to expression or just a set of columns that should turn into [:default] -- you don't need :raw here.

See https://www.john-shaffer.com/honeysql/?q=%7B%3Ainsert-into%20%3Atable%0A%20%3Avalues%20%5B%7B%3Aid%201%2C%3Aname%20%22Sean%22%7D%0A%20%20%20%20%20%20%20%20%20%20%7B%3Aid%20%5B%3Adefault%5D%2C%20%3Aname%20%22Alpox%22%7D%5D%7D&opt=%7B%3Apretty%20true%7D

@alpox ☝️:skin-tone-2: (you can't swap the value in your map for :id, just pointing out the syntax)