# PostgreSQL in Julia: LibPQ.jl
**URL:** https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379
**Category:** Data
**Tags:** package, data
**Created:** [February 27, 2018, 9:36pm UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379 "2018-02-27T21:36:43Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@iamed2](https://discourse.julialang.org/u/iamed2)
#### Post date: [February 27, 2018, 9:36pm UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/1 "2018-02-27T21:36:43Z")
[LibPQ.jl](https://github.com/invenia/LibPQ.jl) v0.3.0 is released so I’m announcing it here!
LibPQ.jl will now be the fastest and easiest way to work with PostgreSQL in Julia. There are still features to be added, but it’s ready for use. Currently your best experience will be using LibPQ in conjunction with DataStreams.
The most common request on the former PostgreSQL.jl was support for other data types, so I’ve made that highly user-customizable and flexible.
Feature requests and feedback are welcome, as are PRs!
---
### Author:  [@ssfrr](https://discourse.julialang.org/u/ssfrr)
#### Post date: [February 27, 2018, 10:54pm UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/2 "2018-02-27T22:54:28Z")
This looks awesome. Really nice and clear documentation.
---
### Author:  [@pazzo83](https://discourse.julialang.org/u/pazzo83)
#### Post date: [February 27, 2018, 10:57pm UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/3 "2018-02-27T22:57:54Z")
Been using this package for a while and it has been excellent!! Thank you for putting it together!!
---
### Author:  [@johann.spies](https://discourse.julialang.org/u/johann.spies)
#### Post date: [May 15, 2018, 8:30am UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/4 "2018-05-15T08:30:08Z")
From my side also thanks for this package. In combination with the new **Octo.jl** ([https://github.com/wookay/Octo.jl/](https://github.com/wookay/Octo.jl/)) package it promises a productive way of communicating with PostgreSQL.
I have a question: How do I do a `COPY From` using LibPQ?
I have tried this (copying a part from a dumpfile):
```julia
r = """CREATE TABLE a_uts (ut text,
id serial);"""
copy = """COPY a_uts (ut, id) FROM stdin;\n"""
d = [["W:000060362500001",1],["W:000060362500002",2],["W:000070603200027",3]]
io = IOBuffer()
write(io,r)
write(io,copy)
for l in d
println(io, join(l,"\t"))
end
print(io, "\\.")
seekstart(io)
s = readstring(io)
result = execute(conn, s)
clear!(result)
```
And get this (expected) error:
```julia
error | LibPQ]: ERROR: syntax error at or near "WOS"
LINE 3: W:000060362500001 1
```
I can use `INSERT` statements, but I have millions of records that I have to process and want to do it at 10000 at a time. I have thought of making a ramdisk and write a csv-file in the ramdisk. That will enable me to use that file in the `COPY`-statement. But there must be a more efficient way of doing it.
---
### Author:  [@iamed2](https://discourse.julialang.org/u/iamed2)
#### Post date: [May 16, 2018, 6:04pm UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/5 "2018-05-16T18:04:54Z")
Hi Johann, thanks for thanks!
Currently I haven’t wrapped the functionality, but it exists in the C library. [PostgreSQL: Documentation: 10: 33.9. Functions Associated with the COPY Command](https://www.postgresql.org/docs/10/static/libpq-copy.html)
I would welcome a PR to wrap it with an IO interface, but you can hack support by using these directly: [https://github.com/invenia/LibPQ.jl/blob/master/src/headers/libpq-fe.jl#L539](https://github.com/invenia/LibPQ.jl/blob/master/src/headers/libpq-fe.jl#L539)
---
### Author:  [@wookyoung](https://discourse.julialang.org/u/wookyoung)
#### Post date: [May 17, 2018, 3:01am UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/6 "2018-05-17T03:01:22Z")
Hi, Johann.
I followed up the links above, (great thanks to iamed2)
this seems to work fine:
```julia
# execute COPY ...
import LibPQ.libpq_c: PQputCopyData, PQputCopyEnd
d = [["W:000060362500001",1], ["W:000060362500002",2], ["W:000070603200027",3]]
for (ut, id) in d
buf = string(join((ut, id), '\t'), '\n')
PQputCopyData(jl_conn.conn, pointer(buf), Cint(length(buf)))
end
PQputCopyEnd(jl_conn.conn, C_NULL)
```
see the following example.
[https://github.com/wookay/Octo.jl/blob/master/test/adapters/postgresql/copy\_test.jl](https://github.com/wookay/Octo.jl/blob/master/test/adapters/postgresql/copy_test.jl)
---
### Author:  [@johann.spies](https://discourse.julialang.org/u/johann.spies)
#### Post date: [May 17, 2018, 7:37am UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/7 "2018-05-17T07:37:31Z")
Fantastic! Thanks @wookyoung and @iamed2!
---
### Author:  [@johann.spies](https://discourse.julialang.org/u/johann.spies)
#### Post date: [July 4, 2018, 9:53am UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/8 "2018-07-04T09:53:14Z")
Following up on this conversation:
I then tried the following on the commandline - bypassing Octo:
```julia
r = """CREATE TABLE IF NOT EXISTS a_uts (ut text, id serial);"""
"CREATE TABLE IF NOT EXISTS a_uts (ut text, id serial);"
julia> copy = """COPY a_uts (ut, id) FROM stdin;\n"""
"COPY a_uts (ut, id) FROM stdin;\n"
julia> d = [["W:000060362500001",1],["W:000060362500002",2],["W:000070603200027",3]]
3-element Array{Array{Any,1},1}:
["W:000060362500001", 1]
["W:000060362500002", 2]
["W:000070603200027", 3]
julia> import LibPQ.libpq_c: PQputCopyData, PQputCopyEnd
julia> execute(conn,r)
PostgreSQL result
julia> execute(conn, copy)
PostgreSQL result
julia> for (ut, id) in d
buf = string(join((ut, id), '\t'), '\n')
PQputCopyData(conn, pointer(buf), Cint(length(buf)))
end
ERROR: MethodError: no method matching unsafe_convert(::Type{Ptr{Nothing}}, ::LibPQ.Connection)
Closest candidates are:
unsafe_convert(::Type{Ptr{Nothing}}, ::Base.RefValue{T}) where T at refvalue.jl:30
unsafe_convert(::Type{Ptr{Nothing}}, ::Base.RefArray{T,A,R} where R where A<:(AbstractArray{T,N} where N)) where T at refpointer.jl:78
unsafe_convert(::Type{Ptr{Nothing}}, ::Base.CFunction) at c.jl:36
...
Stacktrace:
[1] PQputCopyData(::LibPQ.Connection, ::Ptr{UInt8}, ::Int32) at /home/js/.julia/packages/LibPQ/N7lD/src/headers/libpq-fe.jl:540
[2] top-level scope at ./REPL[12]:3
```
What am I doing wrong?
Unfortunately my knowledge of C and Julia is not enough to try a PR.
Regards
Johann
PS This is on `Julia Version 0.7.0-beta.115 (2018-07-02 03:32 UTC)`
---
### Author:  [@iamed2](https://discourse.julialang.org/u/iamed2)
#### Post date: [July 4, 2018, 4:25pm UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/9 "2018-07-04T16:25:03Z")
You need to pull the connection pointer out of the connection to give to `libpq_c` functions. It’s available as `conn.conn`
---
### Author:  [@johann.spies](https://discourse.julialang.org/u/johann.spies)
#### Post date: [July 5, 2018, 6:38am UTC](https://discourse.julialang.org/t/postgresql-in-julia-libpq-jl/9379/10 "2018-07-05T06:38:29Z")
Thanks!