For an insert, sometimes you want default instead of null for nil/missing columns

[seancorfield]

From Slack:

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

Where the first field has an id (already exists in the database) and the second one has no id (yet to be created) I give those fields to honeysql through:

                 (-> (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))

The generated insert statement creates

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

Where the NULL leads to an error due to a missing value for the primary key. If it was instead DEFAULT the id would instead be created by the database.

This does not happen if I have only new fields (fields without ids) because the id field is omitted and the default will be used

[seancorfield]

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.

[seancorfield]

Looking at this, it's important to note that if you use arrays of column values, we prepend nil to rows that are two shorter and we do not know which column values belong to which column names/labels at the point we render the SQL, so this mapping to DEFAULT can only happen for the sequence of hash maps style of VALUES.

[seancorfield]

I'm leaning toward :values-default-columns as the option name and a set of column names (keys) that must identically match the given column names for which a missing value should produce DEFAULT instead of NULL.