# How to quickly bulk insert into postgres

**URL:** <https://discourse.julialang.org/t/how-to-quickly-bulk-insert-into-postgres/15194>\
**Category:** Data\
**Created:** [September 19, 2018, 7:07pm UTC](https://discourse.julialang.org/t/how-to-quickly-bulk-insert-into-postgres/15194 "2018-09-19T19:07:41Z")\
**Posts on this page:** 1\
**Showing post:** 6

<div class="post-metadata">

**Author:** ![lbilli](https://avatars.discourse-cdn.com/v4/letter/l/59ef9b/32.png) [@lbilli](https://discourse.julialang.org/u/lbilli)\
**Post date:** [September 20, 2018, 1:52pm UTC](https://discourse.julialang.org/t/how-to-quickly-bulk-insert-into-postgres/15194/6 "2018-09-20T13:52:53Z")

</div>

Here’s a very minimal working example, with available technology, that uses `COPY` instead of `INSERT INTO` in order to append a DataFrame to an existing PostgreSQL table, assuming all the columns and types are already lined up:

```julia
using LibPQ, CSV, DataFrames

function append(conn::LibPQ.Connection, table::String, df::DataFrame)

  # Serialize DataFrame into a String
  buf = IOBuffer()
  CSV.write(buf, df, header=false)

  data = String(take!(buf))

  # Send COPY statement
  result = execute(conn, "COPY $table FROM STDIN (FORMAT csv, HEADER FALSE)")

  status(result) == LibPQ.libpq_c.PGRES_COPY_IN || error("Failed")

  # Send data
  LibPQ.libpq_c.PQputCopyData(conn.conn, data, Int32(sizeof(data))) == 1 || error("Failed")

  # Signal end-of-data
  LibPQ.libpq_c.PQputCopyEnd(conn.conn, LibPQ.libpq_c.NULL) == 1 || error("Failed")
end

```

In the example the whole DataFrame is converted into one big String before sending it to the database, but it could be easily done incrementally by issuing several  
`PQputCopyData()` for each chunk of data before calling `PQputCopyEnd()`.

---

_[View the full topic](https://discourse.julialang.org/t/how-to-quickly-bulk-insert-into-postgres/15194)._