PostgreSQL in Julia: LibPQ.jl

Specific Domains Data
,
1

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!

29 Likes
2

This looks awesome. Really nice and clear documentation.

1 Like
3

Been using this package for a while and it has been excellent!! Thank you for putting it together!!

1 Like
4

From my side also thanks for this package. In combination with the new 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):

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:

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.

5

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

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

1 Like
6

Hi, Johann.
I followed up the links above, (great thanks to iamed2)

this seems to work fine:

# 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

3 Likes
7

Fantastic! Thanks @wookyoung and @iamed2!

1 Like
8

Following up on this conversation:

I then tried the following on the commandline - bypassing Octo:

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)

9

You need to pull the connection pointer out of the connection to give to libpq_c functions. It’s available as conn.conn

10

Thanks!

1 Like