Fork me on GitHub
Jeff Evans18:02:51

how can I create an insert-into query where one of the column names has a dot in it? running into this when upgrading from 0.9.4 to the latest 1.0.461. ex:

(-> (apply h/columns [:name_first (keyword "name.last") :col2])
    (h/insert-into :properties)
      (lazy-seq [["Jon" "Smith" 34]
                 ["Andrew" "Cooper" 12]
                 ["Jane" "Daniels" 56]]))
["INSERT INTO properties (name_first, name.last, col2) VALUES (?, ?, 34), (?, ?, 12), (?, ?, 56)"

Jeff Evans18:02:14

I want the SQL to be INSERT INTO properties (name_first, "name.last", col2) VALUES (?, ?, 34), (?, ?, 12), (?, ?, 56)

Jeff Evans19:02:59

well, really, all of the column names could be quoted, that would be nice


@jeffrey.wayne.evans Which "latest"? 1.0 or 2.0?

Jeff Evans19:02:05

sorry. 1.0.461


Looking over the 1.0 code, I don't think there's any way to prevent the dot-splitting -- it seems to be guarded by quoting being OFF so it looks like you definitely cannot have quoting ON and avoid the splitting.


You can't avoid it in 2.0 either since insert column names are a context where the namespace portion of a name is ignored -- and you can only avoid dot-splitting if you have a namespace-qualified name, e.g., properties/name.last in your case.


It's pretty deeply baked into HoneySQL, in both versions, that means "table foo, column bar".

Jeff Evans19:02:22

hmm, interesting. thanks for checking. I need to do a bit more homework to understand how it was doing what we wanted before


There were certainly some big changes in how names were handled in the 0.9.x series of releases -- some changes had to be reverted, some spawned extra options and/or dynamic vars.


I don't think I've ever seen a DB schema where column names contain dots tho'...

Jeff Evans21:02:44

oh, I’ve seen every godawful thing like that. and I’m guessing some of my coworkers have too, since there is a test written for it


Could you do (sql/raw "\"name.last\"") as a workaround?


@codonnell Not in the middle of a column name list, no. But you could, possibly, pass a string into columns maybe? (I haven't tried -- that just occurred to me)


Nope, that won't work.


Oh, yeah -- @codonnell wins the prize after all!

user=> (-> (insert-into :properties) (columns :name_first (sql/raw "name.last") :col2) (values [["Jon" "Smith" 34] ["Andrew" "Cooper" 12] ["Jane" "Daniels" 56]]) (sql/format))
["INSERT INTO properties (name_first, name.last, col2) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)" "Jon" "Smith" 34 "Andrew" "Cooper" 12 "Jane" "Daniels" 56]


Although it will not quote it:

user=> (-> (insert-into :properties) (columns :name_first (sql/raw "name.last") :col2) (values [["Jon" "Smith" 34] ["Andrew" "Cooper" 12] ["Jane" "Daniels" 56]]) (sql/format :quoting :ansi))
["INSERT INTO \"properties\" (\"name_first\", name.last, \"col2\") VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)" "Jon" "Smith" 34 "Andrew" "Cooper" 12 "Jane" "Daniels" 56]


So you'd need to do that manually:

user=> (-> (insert-into :properties) (columns :name_first (sql/raw "\"name.last\"") :col2) (values [["Jon" "Smith" 34] ["Andrew" "Cooper" 12] ["Jane" "Daniels" 56]]) (sql/format))
["INSERT INTO properties (name_first, \"name.last\", col2) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)" "Jon" "Smith" 34 "Andrew" "Cooper" 12 "Jane" "Daniels" 56]

Jeff Evans21:02:42

aha, thanks to both of you! should work

Jeff Evans21:02:57

granted, we are actually passing in things that reify ToSql already, so it will take me a bit of time to figure out how to adjust things. but that’s my problem


And in v2:

user=> (-> (insert-into :properties) (columns :name_first [:raw "\"name.last\""] :col2) (values [["Jon" "Smith" 34] ["Andrew" "Cooper" 12] ["Jane" "Daniels" 56]]) (sql/format))
["INSERT INTO properties (name_first, \"name.last\", col2) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)" "Jon" "Smith" 34 "Andrew" "Cooper" 12 "Jane" "Daniels" 56]


@jeffrey.wayne.evans I'm curious what things you have reifying ToSql? That whole system goes away in v2 (and as you can see, sql/raw becomes just a regular "function call" expression -- a.k.a "special syntax" -- in v2).

Jeff Evans22:02:44

well, I’m still quite new to this codebase, so I definitely can’t answer any type of historical/“why” questions, but the relevant sections are and

Jeff Evans22:02:01

er, sorry, this particular one (the Identifier ) is a defrecord, not a reify