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.
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.
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.
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.
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.