This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2024-03-04
Channels
- # aleph (2)
- # announcements (1)
- # biff (1)
- # calva (16)
- # cider (4)
- # clojure (45)
- # clojure-argentina (1)
- # clojure-europe (21)
- # clojure-nl (1)
- # clojure-norway (18)
- # clojure-uk (7)
- # clojurebridge (1)
- # clojurescript (3)
- # clr (3)
- # cursive (21)
- # datomic (4)
- # fulcro (4)
- # graalvm (3)
- # holy-lambda (8)
- # honeysql (22)
- # lsp (3)
- # malli (3)
- # nbb (1)
- # off-topic (3)
- # portal (13)
- # re-frame (3)
- # reagent (7)
- # releases (1)
- # shadow-cljs (1)
Hi guys, quick question. I'm making a compound update statement using honeysql 2.x and I'm having trouble with types. And when I try to cast them as a patch, I get weird errors, so I figured I'd as about the root issue. I am making an update of the form:
(sql/format {:update [:user :u]
:set {:recorded_at :u2.recorded_at
:spend :u2.spend}
:from [[{:values [[#inst "2024-03-01" "account_1" 100 #inst "2024-03-03"]
[#inst "2024-03-01" "account_2" 200 #inst "2024-03-03"]]}
[[:u2 :instant :account :spend :recorded_at]]]]
:where [:and [:= :u.instant :u2.instant] [:= :u.account :u2.account]})
I get the expected vector result, where the parameters are still in datetime objects. However, when I run it with clojure.java.jdbc/execute!
I get the following error
ERROR: operator does not exist: date = text
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 347 Call getNextException to see other errors in the batch.
This is because the sql statement resolves the datetime objects to strings, rather than preserving them as java.sql.date objects, so the statement ends up being this:
UPDATE user u SET recorded_at = u2.recorded_at,
spend = u2.spend
FROM (VALUES ('2024-03-01', 'account_1', 100, '2024-03-03'),
('2024-03-01', 'account_2', 200, '2024-03-03'))
AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account);
To make this work, I need to add explicit timestamp casting in SQL as seen below:
UPDATE user u SET recorded_at = u2.recorded_at,
spend = u2.spend
FROM (VALUES ('2024-03-01'::timestamp, 'account_1', 100, '2024-03-03'::timestamp),
('2024-03-01'::timestamp, 'account_2', 200, '2024-03-03'::timestamp))
AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account);
I am not sure why this process converts the date objects here as when I have a simpler update statement (shown below) there are no issues and the date objects are preserved:
(sql/format {:update :u
:set {:spend 0 :recorded_at #inst "2024-03-03"}
:where [:and [:= :instant #inst"2024-02-29")]
[:= :account "account_1"]]})
Any help would be much appreciated!!The fact that the simpler update works, and the more complex call produces reasonable SQL says this is not a HoneySQL issue. This is about your database driver and the coercions it is prepared to make without hints...
["UPDATE user u SET recorded_at = u2.recorded_at, spend = u2.spend FROM (VALUES (?, ?, ?, ?), (?, ?, ?, ?)) AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account)" #inst "2024-03-01T00:00:00.000-00:00" "account_1" 100 #inst "2024-03-03T00:00:00.000-00:00" #inst "2024-03-01T00:00:00.000-00:00" "account_2" 200 #inst "2024-03-03T00:00:00.000-00:00"]
If you were using next.jdbc
, I'd suggest wrapping the #inst "..."
expressions in as-timestamp
https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.909/api/next.jdbc.types#as-timestamp but clojure.java.jdbc
doesn't provide that sort of hinting so, yes, you're probably going to need to tell HoneySQL to generate casts with [:cast #inst "..." :timestamp]
in this situation.(I will admit, I didn't even realize this SQL was possible ... what database/driver are you using?)
Ah ok interesting. It starts to get a little hairy when I do the casting because I'll cast like so:
(sql/format {:update [:user :u]
:set {:recorded_at :u2.recorded_at
:spend :u2.spend}
:from [[{:values [[[:cast "2024-03-01" :timestamp] "account_1" [:cast 100 :double-precision] [:cast "2024-03-03" :timestamp]]
[[:cast "2024-03-01" :timestamp] "account_2" [:cast 200 :double-precision] [:cast "2024-03-03" :timestamp]]]}
[[:u2 :instant :account :spend :recorded_at]]]]
:where [:and [:= :u.instant :u2.instant] [:= :u.account :u2.account]})
and I'll get an error ERROR: type "double_precision" does not exist
. Which was one of the examples given https://github.com/seancorfield/honeysql/blob/develop/doc/special-syntax.md lolI'm using Postgres 12x with clojure.java.jdbc
The code you're pasting is not syntactically correct, BTW. I had to add a ]
into that last line.
Ah my bad sorry did it on the fly :face_with_peeking_eye:
When I run that code above (with the extra ]
) I get the correct output:
(sql/format {:update [:user :u]
:set {:recorded_at :u2.recorded_at
:spend :u2.spend}
:from [[{:values [[[:cast "2024-03-01" :timestamp] "account_1" [:cast 100 :double-precision] [:cast "2024-03-03" :timestamp]]
[[:cast "2024-03-01" :timestamp] "account_2" [:cast 200 :double-precision] [:cast "2024-03-03" :timestamp]]]}
[[:u2 :instant :account :spend :recorded_at]]]]
:where [:and [:= :u.instant :u2.instant] [:= :u.account :u2.account]]})
["UPDATE user u SET recorded_at = u2.recorded_at, spend = u2.spend FROM (VALUES (CAST(? AS TIMESTAMP), ?, CAST(? AS DOUBLE PRECISION), CAST(? AS TIMESTAMP)), (CAST(? AS TIMESTAMP), ?, CAST(? AS DOUBLE PRECISION), CAST(? AS TIMESTAMP))) AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account)" "2024-03-01" "account_1" 100 "2024-03-03" "2024-03-01" "account_2" 200 "2024-03-03"]
user=>
So it might help if you actually paste in the real code you're using since the code you've posted isn't...
> I'm using Postgres 12x with clojure.java.jdbc
For future debugging, c.j.j is not "the driver" -- that would be the actual org.postgres
dep in your project. But I suspect the driver version isn't relevant at this point.
That's interesting because when I run that code that you posted above, I get the following output (with the double_precision
)
["UPDATE user u SET recorded_at = u2.recorded_at, spend = u2.spend FROM (VALUES (CAST(? AS timestamp), ?, CAST(? AS double_precision), CAST(? AS timestamp)), (CAST(? AS timestamp), ?, CAST(? AS double_precision), CAST(? AS timestamp))) AS U2(instant, account, spend, recorded_at) WHERE (u.instant = u2.instant) AND (u.account = u2.account)" "2024-03-01" "account_1" 100 "2024-03-03" "2024-03-01" "account_2" 200 "2024-03-03"]
> paste in the real code Yeah it just has a ton of functions that calculate the vectors/maps so figured it'd be easier initially. Will get the evaluated map though...
And what version of HoneySQL are you using (exact dependency)?
2.2.840
That's old. Lots of bug fixes and enhancements since then.
2.5.1103 -- 2023-12-03 -- latest released version.
ahh ok, I'll bump it up
2.2.840 is over two years old.
Ok bumped it up and it fixed it. Thanks for the help @U04V70XH6 and sorry for it just being an old version!
NP. c.j.j is no longer maintained so I'd strongly suggest looking at migrating to next.jdbc
if you can.
At least you're already on HoneySQL 2.x instead of 1.x 🙂