This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2023-03-29
Channels
- # admin-announcements (1)
- # announcements (20)
- # babashka (43)
- # beginners (134)
- # calva (2)
- # clerk (7)
- # cljdoc (9)
- # clojars (8)
- # clojure (91)
- # clojure-europe (21)
- # clojure-nl (1)
- # clojure-norway (13)
- # clojure-uk (1)
- # clojurescript (5)
- # datahike (3)
- # docker (2)
- # emacs (6)
- # fulcro (7)
- # graphql (9)
- # honeysql (24)
- # improve-getting-started (5)
- # introduce-yourself (1)
- # lambdaisland (1)
- # luminus (3)
- # malli (3)
- # nbb (19)
- # off-topic (22)
- # pathom (1)
- # portal (3)
- # practicalli (1)
- # rdf (26)
- # reagent (29)
- # reitit (9)
- # shadow-cljs (15)
- # spacemacs (3)
- # sql (4)
- # tools-build (30)
- # xtdb (41)
hitting a wierd issue with next.jdbc / honey I am doing a group by (substring postcode 1 4) and also the same in the select, pushing the query through sql/format gives me a correct sql query I can run against my db how ever sending it to execute! I get this error.
ERROR: column "accounts_address.postcode" must appear in the GROUP BY clause or be used in an aggregate function
Which would usually make sense except the query works and I do not need postcode in the group by so feels like I am hitting some caveat or bug or something.
Anyone have any suggestions ?That's really weird indeed. Are you sure you're not accidentally executing some outdated code that's still referenced from somewhere else?
that's did not help got a bit further, tried this
GROUP BY SUBSTRING(\"accounts_address\".\"postcode\", 1, 4)"])
vs
GROUP BY SUBSTRING(\"accounts_address\".\"postcode\", ?, ?)" 1 4])
so something todo with the dynamic paramsSo what is the difference exactly, in code, between the version that works and the version that doesn't?
one has the values as part of the query the other has them as parameters, ie with ? and with out
I would be happy for works at this point, but equally curious what I am doing wrong as feels like it should work
It might be that the DB can't figure out the types of those ?
for sure because the substring
function is overloaded.
Maybe it'll help providing explicit casts, like substring(x, ?::int, ?::int)
, but no idea.
Using :cast how ever gives me the same error,
(sqlh/group-by [:substring :accounts_address.postcode
[:cast 1 :int]
[:cast 4 :int]
#_#_[:inline 1]
[:inline chars]])
So i guess I will use inline for now, cheers for the assistance appreciated 🙂@UU67HFS2X I've seen that with different versions of MySQL: older versions allowed you to omit grouped columns/expressions from the SELECT
but later versions required you provide them (although there was a DB setting to relax the new restriction). I think if you add that [:substring ..]
call into the :select
with an alias, you will be able to :group-by
that alias.
does the same apply for postgres ? that's my target in this instance I also realized the cast above is wrong as it should be ::integer not ::int when using the cast function so will need to retry that
Given the ERROR: column "accounts_address.postcode" must appear in the GROUP BY clause or be used in an aggregate function
error you're getting, I'm going to say "Yes, it applies to PostgreSQL" 🙂
if you have not thought of doing this, using the chat gpt style apps are pretty good at generating you honeysql code with the correct format, I just asked you chat this question.
give me an example of using a summed case statement using honeysql version 2
Which saved me piecing the parts together, surprised how good it is considering honeysql is a bit more nicheha, very impressed it does entire queries and if you specify give it to me in threading macro style it can even use the helper functions which are much nicer to read 🙂
Beware of very confident but completely wrong answers from ChatGPT: it will happily invent new functions in libraries, just on the basis that the names sound like they solve the problem -- remember that it is a language model and has no real understanding of Clojure or the specifics of any library!