Fork me on GitHub
#sql
<
2020-03-13
>
Ahmed Hassan13:03:42

guides on date and time in postgresql?

jumar16:03:36

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/

eval-on-point14:03:41

We are using postgres and have success just converting whatever object we have to a timestamp (string) and inserting that

kwladyka21:03:25

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?

seancorfield21:03:00

What DB are you using @kwladyka?

seancorfield21:03:02

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

kwladyka21:03:42

(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid IN(?)") (map uuid/as-uuid uuids)])
what is wrong here?

kwladyka21:03:57

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.

seancorfield21:03:13

Did you actually read the docs I linked to?

kwladyka21:03:17

oh so maybe jdbc conver it right, but still have issue about ::uuid

kwladyka21:03:43

sure, but probably I didn’t understand

seancorfield21:03:02

Read it again. Carefully.

seancorfield21:03:10

And read my note above about object-array.

kwladyka21:03:29

ok this is first time when I use object-array - I have no idea what it does, but I have:

kwladyka21:03:36

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.

seancorfield21:03:29

Your SQL is still wrong I expect.

kwladyka21:03:26

yes, I have no idea how to write this to let know postgresql this is uuid not string

seancorfield21:03:37

The docs I linked to show you.

kwladyka21:03:42

(jdbc/execute-one! db [(str "SELECT * FROM " (name table) " WHERE uuid = ?::uuid") (uuid/as-uuid uuid)])
easy here

kwladyka21:03:45

sorry, don’t get it

kwladyka21:03:07

I don’t know how to create this objects I guess

seancorfield21:03:49

First off, the docs say that in your SQL you need WHERE uuid = ANY(?)

kwladyka21:03:31

Oh I misinterpreted this, but still doesn’t work

seancorfield21:03:46

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

seancorfield21:03:18

That will depend on what your uuids variable contains -- is it objects or strings at that point?

kwladyka21:03:24

I can have whichever will be easier string / #uuid

seancorfield21:03:29

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.

seancorfield21:03:49

I don't use PG so you'll have to figure this out based on the information I've given you.

kwladyka21:03:06

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

kwladyka21:03:25

into-array the same

kwladyka21:03:36

(jdbc/execute-one! db ["SELECT * FROM shops WHERE uuid = ANY(?)" (into-array #{#uuid "00000000-0000-0000-0000-000000000001", #uuid "00000000-0000-0000-0000-000000000000"})])

kwladyka21:03:50

this is my current state and fighting with this

seancorfield21:03:18

Then you may need the "some type" variant I mentioned above

seancorfield21:03:33

(but I've no idea what UUID type you'll need there)

kwladyka21:03:40

yes, trying to figure out how to write this

kwladyka21:03:39

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

kwladyka21:03:09

Can'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.

seancorfield21:03:10

Good ol' PostgreSQL strikes again... ¯\(ツ)

seancorfield21:03:38

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)

kwladyka22:03:52

no way to make uuid to be understandable by jdbc?

kwladyka22:03:04

any hints how it can be done?

kwladyka22:03:10

Similar to JSON example?

kwladyka22:03:21

this is the part with I don’t have too much experience

seancorfield22:03:35

Each database is different.

seancorfield22:03:46

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 "?".

kwladyka22:03:08

there is 1 thing which I can’t understand

kwladyka22:03:02

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

kwladyka22:03:11

so it looks like this understand #uuid

kwladyka22:03:16

but not when in array

seancorfield22:03:34

Did you try the repeat variant I posted above?

kwladyka22:03:40

yes, it works

seancorfield22:03:47

Then you have a solution.

kwladyka22:03:57

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

seancorfield22:03:07

Good. Chalk the rest up to PostgreSQL/JDBC weirdness. Sometimes JDBC stuff is just... tears hair out 🔥

😟 4
kwladyka22:03:23

Thank you for help. I will still try to find the solution with array, but I don’t give myself too much chance 🙂

kwladyka22:03:05

this one work

(jdbc/execute-one! db ["SELECT * FROM shops WHERE uuid = ANY(?::uuid[])" "{00000000-0000-0000-0000-000000000001,00000000-0000-0000-0000-000000000000}"])

kwladyka22:03:24

not perfect but good enough

seancorfield22:03:41

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...

kwladyka22:03:39

beucase it work with #uuid object

kwladyka22:03:45

but not when I use ANY

kwladyka22:03:51

so with arrray

kwladyka22:03:59

Can'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. using array as an input always throw error like this