# Seeking assistance to INSERT data into Postgres DB using LibPQ.jl

**URL:** <https://discourse.julialang.org/t/seeking-assistance-to-insert-data-into-postgres-db-using-libpq-jl/84510>\
**Category:** Data\
**Tags:** question, postgresql\
**Created:** [July 20, 2022, 6:22am UTC](https://discourse.julialang.org/t/seeking-assistance-to-insert-data-into-postgres-db-using-libpq-jl/84510 "2022-07-20T06:22:32Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![adenoz](https://avatars.discourse-cdn.com/v4/letter/a/22d042/32.png) [@adenoz](https://discourse.julialang.org/u/adenoz)\
**Post date:** [July 20, 2022, 6:22am UTC](https://discourse.julialang.org/t/seeking-assistance-to-insert-data-into-postgres-db-using-libpq-jl/84510/1 "2022-07-20T06:22:32Z")

</div>

Hi,

Firstly, I am still learning Julia and my recent attempts to expand into Julia is through testing how to write data into a PostgreSQL database. I have been able to successfully connect to and query data in a PG database.

However, I have been attempting to write some simple data to a PostgreSQL database using LibPQ.jl and have been unsuccessful. It is simple test data. I _think_ I am having problems around the use of quotation marks, but I may very well be wrong. Here is an example of what I have tried.

```sql
LibPQ.load!(
    (dmdate = '2022-07-17', dmmetric = "overall", dmnum = 8),
	conn,
	"INSERT INTO dailymetrics (dmdate, dmmetric, dmnum) VALUES (\$1, \$2, \$3);")

```

In PG, the dmdate is a date, dmmetric is a varchar(20) and dmnum is an integer.

I am getting errors if I use single quotations around ‘overall’ because in Julia I know I need to use double quotes. However I am also getting errors when using single quotes around the date (“syntax: character literal contains multiple characters”). I seem to get different errors when I make Julia happy due to postgesql expectations around formating. I get argument errors that don’t satisfy the Tables.jl ‘AbstractionRow’ interface. I suspect this is due to when writing SQL I need to use single quotes around both the date and string (varchar). But I may be wrong on that. I’ve tried all variations and combinations of quotation marks.

Instead of LibPQ.load I’ve also tried variations around Data.stream to no avail. I feel like I am close but cannot work it out. There may be another way I can specify type but I cannot find how to do that.

I’d appreciate any assistance in fixing this.

---

<div class="post-metadata">

**Author:** ![adenoz](https://avatars.discourse-cdn.com/v4/letter/a/22d042/32.png) [@adenoz](https://discourse.julialang.org/u/adenoz)\
**Post date:** [July 21, 2022, 12:26am UTC](https://discourse.julialang.org/t/seeking-assistance-to-insert-data-into-postgres-db-using-libpq-jl/84510/2 "2022-07-21T00:26:56Z")

</div>

Well, I have ruled out the types or quotation marks being the problem.

```sql
LibPQ.load!(
	(dmdate = Date("2022-07-17"), dmmetric = String("overall"), dmnum = Int(8)),
	conn,
	"INSERT INTO dailymetrics (dmdate, dmmetric, dmnum) VALUES (\$1, \$2, \$3);")

```

Now I am getting another error.

IOError: stream is closed or unusable

1. **check\_open** @_stream.jl:386_ [inlined]
2. **uv\_write\_async** (::Base.PipeEndpoint, ::Ptr{UInt8}, ::UInt64)@_stream.jl:1018_
3. **uv\_write** (::Base.PipeEndpoint, ::Ptr{UInt8}, ::UInt64)@_stream.jl:981_
4. **unsafe\_write** (::Base.PipeEndpoint, ::Ptr{UInt8}, ::UInt64)@_stream.jl:1064_
5. **unsafe\_write** @_io.jl:362_ [inlined]
6. **write** @_io.jl:244_ [inlined]
7. **print** @_io.jl:246_ [inlined]
8. **var"#with\_output\_color#873"** (::Bool, ::Bool, ::Bool, ::Bool, ::Bool, ::typeof(Base.with\_output\_color), ::Function, ::Symbol, ::IOContext{Base.PipeEndpoint}, ::String)@_util.jl:106_
9. **#printstyled#874** @_util.jl:129_ [inlined]
10. **emit** (::Memento.DefaultHandler{Memento.DefaultFormatter, IOContext{Base.PipeEndpoint}}, ::Memento.DefaultRecord)@_handlers.jl:211_
11. **log** (::Memento.DefaultHandler{Memento.DefaultFormatter, IOContext{Base.PipeEndpoint}}, ::Memento.DefaultRecord)@_handlers.jl:44_
12. **log** (::Memento.Logger, ::Memento.DefaultRecord)@_loggers.jl:371_
13. **\_log** (::Memento.Logger, ::String, ::String)@_loggers.jl:416_
14. **log** (::Memento.Logger, ::String, ::String)@_loggers.jl:395_
15. **error** (::Memento.Logger, ::LibPQ.Errors.PQResultError{LibPQ.Errors.C08, LibPQ.Errors.E08P01})@_loggers.jl:462_
16. **var"#handle\_result#50"** (::Bool, ::typeof(LibPQ.handle\_result), ::LibPQ.Result{false})@_results.jl:238_
17. **var"#execute#70"** (::Bool, ::Bool, ::Base.Pairs{Symbol, Union{}, Tuple{}, NamedTuple{(), Tuple{}}}, ::typeof(LibPQ.execute), ::LibPQ.Statement, ::Vector{Union{Missing, String}})@_statements.jl:129_
18. **load!** (::NamedTuple{(:dmdate, :dmmetric, :dmnum), Tuple{Dates.Date, String, Int64}}, ::LibPQ.Connection, ::String)@_tables.jl:166_
19. **top-level scope** @_[Local: 1](http://localhost:1234/edit?id=bea2f066-0884-11ed-1f2c-f1253c7d04ae#)_ [inlined]

I have no idea what this means. When I do `LibPQ.status(conn)` I get 'CONNECTION\_OK`.

Anyone have any ideas?

---

<div class="post-metadata">

**Author:** ![ImreSamu](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/imresamu/32/20677_2.png) [@ImreSamu](https://discourse.julialang.org/u/ImreSamu)\
**Post date:** [July 21, 2022, 3:20am UTC](https://discourse.julialang.org/t/seeking-assistance-to-insert-data-into-postgres-db-using-libpq-jl/84510/3 "2022-07-21T03:20:12Z")

</div>

> [@adenoz](#):
>
> `dailymetrics`

This code is working for me; so you can compare with your code.

```julia

using LibPQ
using Dates
using DataFrames
conn = LibPQ.Connection("dbname=pljulia")
result = execute(conn, """   
    DROP TABLE IF EXISTS test_dailymetrics;
    CREATE TABLE test_dailymetrics (
        dmdate DATE,
        dmmetric TEXT,
        dmnum integer
    );
    """,
    throw_error=true
)
LibPQ.load!(
    ( dmdate = [Date(2022, 7, 17)] 
        , dmmetric = ["overall"] 
        , dmnum = [8] 
    ),
	conn,
	"INSERT INTO test_dailymetrics (dmdate, dmmetric, dmnum) VALUES (\$1, \$2, \$3);"
)
df=DataFrame(execute(conn, "SELECT * FROM test_dailymetrics"));
df 
close(conn)

```

### log

```julia
julia> using LibPQ

julia> using Dates

julia> using DataFrames

julia> conn = LibPQ.Connection("dbname=pljulia")
PostgreSQL connection (CONNECTION_OK) with parameters:
  user = pljulia
  password = ********************
  channel_binding = prefer
  dbname = pljulia
  hostaddr = 127.0.0.1
  port = 5432
  client_encoding = UTF8
  options = -c DateStyle=ISO,YMD -c IntervalStyle=iso_8601 -c TimeZone=UTC
  application_name = LibPQ.jl
  sslmode = prefer
  sslcompression = 0
  sslsni = 1
  ssl_min_protocol_version = TLSv1.2
  gssencmode = prefer
  krbsrvname = postgres
  target_session_attrs = any

julia> result = execute(conn, """   
           DROP TABLE IF EXISTS test_dailymetrics;
           CREATE TABLE test_dailymetrics (
               dmdate DATE,
               dmmetric TEXT,
               dmnum integer
           );
           """,
           throw_error=true
       )
PostgreSQL result

julia> LibPQ.load!(
           ( dmdate = [Date(2022, 7, 17)] 
               , dmmetric = ["overall"] 
               , dmnum = [8] 
           ),
               conn,
               "INSERT INTO test_dailymetrics (dmdate, dmmetric, dmnum) VALUES (\$1, \$2, \$3);"
       )
PostgreSQL prepared statement named __libpq_stmt_0__ with query INSERT INTO test_dailymetrics (dmdate, dmmetric, dmnum) VALUES ($1, $2, $3);

julia> df=DataFrame(execute(conn, "SELECT * FROM test_dailymetrics"));

julia> df
1×3 DataFrame
 Row │ dmdate dmmetric dmnum  
     │ Date? String? Int32? 
─────┼──────────────────────────────
   1 │ 2022-07-17 overall 8

julia> close(conn)

julia> 

```

some hints:

- in the testcode [LibPQ.jl/test/runtests.jl at master · JuliaDatabases/LibPQ.jl · GitHub](https://github.com/invenia/LibPQ.jl/blob/master/test/runtests.jl) .. there are lot of examples.
- Insertion example in the Doc : [https://invenia.github.io/LibPQ.jl/stable/#Insertion](https://invenia.github.io/LibPQ.jl/stable/#Insertion)
- [LibPQ.load! doc](https://invenia.github.io/LibPQ.jl/stable/pages/api/#LibPQ.load!)
  - Julia Tables doc: [Home · Tables.jl](https://tables.juliadata.org/stable/)

---

<div class="post-metadata">

**Author:** ![adenoz](https://avatars.discourse-cdn.com/v4/letter/a/22d042/32.png) [@adenoz](https://discourse.julialang.org/u/adenoz)\
**Post date:** [July 21, 2022, 3:54am UTC](https://discourse.julialang.org/t/seeking-assistance-to-insert-data-into-postgres-db-using-libpq-jl/84510/4 "2022-07-21T03:54:53Z")

</div>

Hey thanks so much for your assistance. I did try to replicate your code for the LibPQ.load! function and I continued to get an error. So then because I am only testing I did use your result=execute(conn,…) function as well which dropped the table if exists and created a fresh one. That worked. Then I ran the LibPQ.load! and then it worked as well without any additional changes! So that is all good.

It appears that I did need to run that execute(conn…) section at least once. However I am aiming to have this script run daily and update the dailymetrics table. I don’t want to DROP TABLE IF EXISTS. So, I disabled the result=execute(conn…) function, shut down the script then ran it again and it worked without needing the result = execution(conn…). So that’s great. But I DID need to run at at least once it seems. I can only imagine there is some ownership or permissions issue with Postgres causing LibPQ.load! code to not run initially. I did go into Postgres and confirm both my dailymetrics and postgres user had INSERT privileges and they both did.

So now it is all seeming to work! I don’t really understand why the execute(conn) needed to run first but I may look into that later. And thanks for those links. I had read the LibPQ docs on Github, which I found dense to get through as a fresh user of the package. I did also see the result = execute(conn…) function but had incorrectly thought it would not be necessary.

I’ll stop waffling now but thanks again so much. I was really stuck.

---

<div class="post-metadata">

**Author:** ![wookyoung](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/wookyoung/32/157_2.png) [@wookyoung](https://discourse.julialang.org/u/wookyoung)\
**Post date:** [July 21, 2022, 5:22pm UTC](https://discourse.julialang.org/t/seeking-assistance-to-insert-data-into-postgres-db-using-libpq-jl/84510/5 "2022-07-21T17:22:48Z")

</div>

I made an example using Octo.jl that providing more simple way such insert things.

> <https://github.com/wookay/Octo.jl/blob/master/test/adapters/postgresql/discourse_84510.jl>

---

<div class="post-metadata">

**Author:** ![adenoz](https://avatars.discourse-cdn.com/v4/letter/a/22d042/32.png) [@adenoz](https://discourse.julialang.org/u/adenoz)\
**Post date:** [July 22, 2022, 4:26am UTC](https://discourse.julialang.org/t/seeking-assistance-to-insert-data-into-postgres-db-using-libpq-jl/84510/6 "2022-07-22T04:26:22Z")

</div>

Hey thanks for that. As a new Julia user, I wasn’t tracking Octo as a thing. I’ll look into that.

I might add for others that follow, I have my code all working well for now and I’ve simplified it slightly to draw on a dataframe that gets generated earlier in my script.

```sql
LibPQ.load!(
	df, # this is the new bit, drawing on a dataframe rather than write it inside this load function.
	conn,
	"INSERT INTO dailymetrics (dmdate, dmmetric, dmnum) VALUES (\$1, \$2, \$3);")

```

I think where I was running into trouble is that I was playing around in a Pluto notebook and I wasn’t being careful about opening and closing the LibPQ connection to the database. I still have to explore this deeper but the connection can actually be hard to disconnect then reconnect to a different table unless you are very deliberate and careful.

But I now have all my code for this in a function where it establishes the connection, writes to the database, queries it to confirm the data has been written, then closes the connection, then returns a string output depending on successful read or not.