Skip to content

Instantly share code, notes, and snippets.

@apeckham
Last active June 9, 2022 23:42
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save apeckham/d4c0005545241d97757ce66ab75c602f to your computer and use it in GitHub Desktop.
Save apeckham/d4c0005545241d97757ce66ab75c602f to your computer and use it in GitHub Desktop.
clojure jdbc copy into temporary table with upsert
(def t (java.io.File/createTempFile "filename" ".txt"))
(println (.getPath t))
(defn row [n]
[(+ n 500000) (rand-int 10000) (rand-int 10000) (rand-int 10000) (rand-int 10000) 5550555 (rand) "hello world"])
(with-open [w (io/writer t)]
(csv/write-csv w (map row (range 1000000))))
(j/with-db-transaction [tx db-spec]
(j/execute! tx "create temporary table x (like t)")
(def cm (org.postgresql.copy.CopyManager. (j/get-connection tx)))
(.copyIn cm "copy x (id, i, i2, i3, i4, i5, i6, i7) from stdin csv" (io/reader t))
(j/execute! tx "insert into t select * from x on conflict (id) do update set i=excluded.i, i2=excluded.i2, i3=excluded.i3, i4=excluded.i4, i5=excluded.i5, i6=excluded.i6, i7=excluded.i7"))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment