2025-03-07 sql | Clojure Slack Archive

sql

jussi 2025-03-07T11:02:55.139769Z

Been a long time since working with raw SQL, it is a breeze with next-jdbc, but I'm stumbling now with execute-batch! and postgresql timestamp formats. Our document date is a date without time component (intentionally, eg. "2025-03-07"). I add the time component before inserting, like this

(-> (:date document)
     tick/date
     (tick/at "10:00")
     tick/inst)

And then the batch insert as follows

(next/with-transaction [tx (create-jdbc-postgres-datasource cache-config)]
  (log/info "Inserting " (count coll) " documents to cache db.")
  (let [parameters (mapcat (fn [[exp res]] (to-insert-batch-parameters exp res)) coll)]
    (next/execute-batch! tx insert-into-sql parameters {})))

I've required [next.jdbc.date-time] as suggested in the docs to get some magic for date conversions, but I still get errors and now I'm wondering does the date-time magic reach batch insert or do I have to emit the date as a java.util.Date and rely on the JDCB-driver? The column is of TIMESTAMP format.

ERROR: column "calculated_at" is of type timestamp without time zone but expression is of type numeric
  Hint: You will need to rewrite or cast the expression.

What am I missing here?

p-himik 2025-03-07T11:06:44.142339Z

> do I have to emit the date as a java.util.Date Should probably be java.sql.Date.

p-himik 2025-03-07T11:09:35.690409Z

A quick look at the impl suggests that requiring next.jdbc.date-time should make it all work with execute-batch! as well since the parameters passed to that function still go through set-parameter. java.sql.Date is the only type that uses .setDate and not .setTimestamp.

jussi 2025-03-07T11:11:19.684609Z

Thanks, my initial look on the impl also suggested that it should work. Good point on the java.sql.Date

p-himik 2025-03-07T11:13:02.469929Z

Also, just in case - there's no real need in tick with modern java.time.*. Despite interop, I personally find the latter much clearer w.r.t. intent and outcomes.

jussi 2025-03-07T11:18:56.723829Z

Agree, tick was introduced ~4 years ago so following local code base conventions for now.

jussi 2025-03-07T12:34:26.020779Z

My data looks correct albeit I've been staring at it for the last few hours so I might just not see something. This is that is printed out using println , first is the raw value, type is from type

ts #inst "2025-03-07T12:13:07.289-00:00" type java.util.Date

p-himik 2025-03-07T12:40:29.411319Z

Just to double-check something - what does (-> next.jdbc.prepare/SettableParameter :impls (get java.util.Date) :set-parameter) return?

jussi 2025-03-07T12:41:10.937029Z

Aaaaand, PEBKAC. picard-facepalm My sql string had two columns in wrong order...

😄 1

jussi 2025-03-07T12:41:34.171119Z

or the parameter list awesome depends how one wants to interpret it

Clojurians Log v2

sql