Fork me on GitHub
#sql
<
2020-01-03
>
exit217:01:23

Hey there, does anyone know if clojure jdbc has an option insert-multi that has update or insert?

exit217:01:55

After looking at the docs, it appears that there isn’t - but I can write one based around the insert fn

seancorfield17:01:20

@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?

seancorfield17:01:31

Both of those support insert-multi!

seancorfield17:01:52

Or perhaps you mean clojure.jdbc which hasn't been updated for years now?

exit217:01:41

@seancorfield I’m referring to clojure.java.jdbc

exit217:01:45

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!

exit217:01:53

but that refers to single entries, afaik

seancorfield17:01:35

upsert SQL is not portable across databases in general which is why neither of my libs has it built in.

exit217:01:50

makes sense

exit217:01:55

I’m using postgres in this case

seancorfield17:01:25

Although I test both against PG, I've never used that DB myself so I can't suggest appropriate SQL, sorry.

exit217:01:05

and modify it a bit to my needs

4
exit219:01:57

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

exit219:01:06

my issue that when I go to overwrite an existing entry I get an error, since its trying an insert

seancorfield19:01:11

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.

seancorfield19:01:28

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.

seancorfield19:01:07

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.

seancorfield19:01:31

Whatever you end up with, you'll have some SQL that you will need to use execute! on.

👍 4
seancorfield19:01:32

(also, the best strategy for upsert can change depending on how your table is constructed in terms of keys/auto-generation/nullable columns etc)