Doesn't seem like there's a way to do it without putting the whole ON CONFLICT into :raw, so might be something that warrants HoneySQL's DSL extension.

@U0522TWDA I'm fixing this so that this works:

user=> (sql/format {:on-conflict [[:date_trunc [:inline "month"] :my_column]]} {:quoted true})
["ON CONFLICT DATE_TRUNC('month', \"my_column\")"]

Does it specifically need those ( .. ) around the expression to be valid syntax?

https://github.com/seancorfield/honeysql/issues/494 (I'm going to assume it does need the parens).

It does, yeah.

Here's how it works now on the latest develop or the latest SNAPSHOT on Clojars:

user=> (sql/format {:on-conflict [:a [:date_trunc [:inline "month"] :my_column] :b]} {:quoted true})
["ON CONFLICT (\"a\", DATE_TRUNC('month', \"my_column\"), \"b\")"]

I'll cut a new release before the end of the month. Not sure what else I'll end up fixing in that release right now.

Not sure how to test it properly given that I can't use date_trunc in ON CONFLICT without also creating an index on it, and I can't create an index on it because the function is not marked as immutable. But given the syntax description of INSERT, it seems that you need an extra pair of parents around expressions in ON CONFLICT. Just tried it with col + 1 expression instead of date_trunc - yep, needs extra parens, so the above should be formatted as ON CONFLICT ("a", (DATE_TRUNC('month', "my_column")), "b"). If it's any help, at least in the case of PostgreSQL the whole part after ON CONFLICT follows the CREATE INDEX format as per the docs.

Oh, interesting... it needs parens inside the group too??

(and, no, matching create index doesn't help at all 🙂 )

Yeah, otherwise stuff like INSERT INTO t VALUES (1) ON CONFLICT (d + 1) DO NOTHING; fails with ERROR: syntax error at or near "+" Position: 41. Using ((d + 1)) works.

Fix pushed to GH. A new SNAPSHOT will be available "soon".

Sorry for the late reply. I see you figured everything out. Many thanks to both of you! I'll try the snapshot ASAP

It works like a charm! thank you!