This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2020-01-03
Channels
- # announcements (2)
- # babashka (66)
- # beginners (225)
- # braveandtrue (1)
- # calva (14)
- # circleci (1)
- # clj-kondo (36)
- # cljsrn (3)
- # clojure (423)
- # clojure-finland (7)
- # clojure-nl (1)
- # clojure-spec (14)
- # clojure-survey (41)
- # clojure-sweden (2)
- # clojure-uk (13)
- # clojurescript (59)
- # community-development (10)
- # cursive (2)
- # datascript (14)
- # datomic (63)
- # events (3)
- # expound (8)
- # figwheel-main (6)
- # kaocha (8)
- # luminus (6)
- # malli (1)
- # nrepl (2)
- # off-topic (51)
- # other-lisps (3)
- # reagent (16)
- # shadow-cljs (44)
- # spacemacs (7)
- # sql (22)
- # vrac (1)
Hey there, does anyone know if clojure jdbc has an option insert-multi
that has update or insert?
After looking at the docs, it appears that there isn’t - but I can write one based around the insert fn
@njj Which library are you referring to? clojure.java.jdbc
(Contrib -- well-established, well-maintained, heavily-used stable), next.jdbc
(the next generation of clojure.java.jdbc
, very actively maintained, well-tested in production), or something else?
Both of those support insert-multi!
Or perhaps you mean clojure.jdbc
which hasn't been updated for years now?
@seancorfield I’m referring to clojure.java.jdbc
https://cljdoc.org/d/org.clojure/java.jdbc/0.7.11/api/clojure.java.jdbc#insert-multi!
I’m currently using the insert-multi!
fn from that lib, but I want to write a version that will handle update or insert. I’m doing batch inserts to a db, but sometimes it will be an update. I saw the example of doing update-or-insert!
upsert
SQL is not portable across databases in general which is why neither of my libs has it built in.
Although I test both against PG, I've never used that DB myself so I can't suggest appropriate SQL, sorry.
No worries, I think I can use this example http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql.html#using-transactions
@seancorfield The only thing bad about this is I feel like mapping over my items and doing single inserts is going to be much slower than using insert-multi!
Any ideas on how to modify that fn to do an update if the value exists on insert? (Or I guess it would try update first, then insert)
my issue that when I go to overwrite an existing entry I get an error, since its trying an insert
As I said, upserts are DB-specific so how you do it efficiently for PostgreSQL will be different to any of the databases I use.
I looked into multi upsert briefly for MySQL (which is what I use at work) and it's all non-standard SQL and has a variety of caveats about the edge cases that do/don't work. I expect the same is true of PG. And SQL Server. Etc.
And, yes, doing individual upserts is very likely going to be much slower than a batch upsert -- if you can figure out how PG does that.
Whatever you end up with, you'll have some SQL that you will need to use execute!
on.
(also, the best strategy for upsert can change depending on how your table is constructed in terms of keys/auto-generation/nullable columns etc)