This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2020-03-13
Channels
- # aleph (16)
- # announcements (8)
- # aws (5)
- # babashka (54)
- # beginners (48)
- # calva (7)
- # cider (7)
- # clojure (209)
- # clojure-brasil (4)
- # clojure-europe (20)
- # clojure-italy (12)
- # clojure-nl (21)
- # clojure-uk (69)
- # clojurescript (24)
- # cursive (11)
- # datascript (7)
- # datomic (47)
- # emacs (14)
- # graphql (20)
- # hoplon (25)
- # jobs (1)
- # kaocha (1)
- # leiningen (14)
- # meander (7)
- # off-topic (44)
- # other-languages (1)
- # pathom (20)
- # re-frame (2)
- # reagent (51)
- # reitit (3)
- # remote-jobs (1)
- # shadow-cljs (46)
- # spacemacs (5)
- # sql (65)
- # tools-deps (86)
- # vim (11)
guides on date and time in postgresql?
This one was helpful for me: https://www.postgresqltutorial.com/postgresql-timestamp/ And this is very enlightening although not about PostgreSQL specifically: https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/
We are using postgres and have success just converting whatever object we have to a timestamp (string) and inserting that
Is there a way to use jdbc to query using multiple uuid?
(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid IN(?)") (clojure.string/join "," (map uuid/as-uuid uuids))])
How this should look? Has jdbc some magic to convert collection in right way, so I can not use join
?What DB are you using @kwladyka?
Read https://cljdoc.org/d/seancorfield/next.jdbc/1.0.395/doc/getting-started/tips-tricks#postgresql which talks about IN
and an array of values.
The example there uses int-array
but you'd need object-array
for UUIDs (and you'll need to pass them as objects I suspect -- no idea how PG handles that).
(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid IN(?)") (map uuid/as-uuid uuids)])
what is wrong here?Can't infer the SQL type to use for an instance of clojure.lang.LazySeq. Use setObject() with an explicit Types value to specify the type to use.
Did you actually read the docs I linked to?
Read it again. Carefully.
And read my note above about object-array
.
ok this is first time when I use object-array
- I have no idea what it does, but I have:
Can't infer the SQL type to use for an instance of [Ljava.lang.Object;. Use setObject() with an explicit Types value to specify the type to use.
Your SQL is still wrong I expect.
yes, I have no idea how to write this to let know postgresql this is uuid
not string
The docs I linked to show you.
(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid = ?::uuid") (uuid/as-uuid uuid)])
easy hereFirst off, the docs say that in your SQL you need WHERE uuid = ANY(?)
Then you need either an Object array with your UUID objects in it, or you might need an array of UUID objects that is typed for PostgreSQL
That will depend on what your uuids
variable contains -- is it objects or strings at that point?
Either (object-array uuids)
or (into-array uuids)
might work, or you might need (into-array SomeType uuids)
where SomeType
is whatever UUID type is acceptable to PostgreSQL.
I don't use PG so you'll have to figure this out based on the information I've given you.
Can't infer the SQL type to use for an instance of [Ljava.lang.Object;. Use setObject() with an explicit Types value to specify the type to use.
this is what I have with object-array
(jdbc/execute-one! db ["SELECT * FROM shops WHERE uuid = ANY(?)" (into-array #{#uuid "00000000-0000-0000-0000-000000000001", #uuid "00000000-0000-0000-0000-000000000000"})])
Then you may need the "some type" variant I mentioned above
(but I've no idea what UUID type you'll need there)
(jdbc/execute-one! db ["SELECT * FROM shops WHERE uuid = ANY(?)" (into-array java.util.UUID #{#uuid "00000000-0000-0000-0000-000000000001", #uuid "00000000-0000-0000-0000-000000000000"})])
doesn’t workCan't infer the SQL type to use for an instance of [Ljava.util.UUID;. Use setObject() with an explicit Types value to specify the type to use.
Good ol' PostgreSQL strikes again... ¯\(ツ)/¯
You could always break down and do this:
(jdbc/execute-one! db (into ["SELECT * FROM shops WHERE uuid IN (" (str/join "," (repeat (count uuids) "?")) ")"] (map uuid/as-uuid uuids)))
(off the top of my head)Each database is different.
The variant I gave with repeat
etc should work just like the single UUID example you had working above. You may need "?::uuid"
instead of just plan "?"
.
this works
(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid = ?") (uuid/as-uuid uuid)])
this too
(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid = ?::uuid") uuid])
this NOT
(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid = ?") uuid])
Did you try the repeat
variant I posted above?
Then you have a solution.
(jdbc/execute! db (into ["SELECT * FROM shops WHERE uuid IN (?,?)"] (map uuid/as-uuid #{#uuid "00000000-0000-0000-0000-000000000001", #uuid "00000000-0000-0000-0000-000000000000"})))
Good. Chalk the rest up to PostgreSQL/JDBC weirdness. Sometimes JDBC stuff is just... tears hair out 🔥
Thank you for help. I will still try to find the solution with array, but I don’t give myself too much chance 🙂
this one work
(jdbc/execute-one! db ["SELECT * FROM shops WHERE uuid = ANY(?::uuid[])" "{00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000000}"])
Interesting. So I guess that's a PG cast to an array type? Kind of weird that it takes a string of that format tho'... with braces around a comma-separated list...
https://clojurians.slack.com/archives/C1Q164V29/p1584137042188400 still this one confuse me