Updating Local Postgresql Database with R, using Update or Insert

Question

I'm working on establishing a local server in order to interact with hosted tables locally and remotely. I've successfully connected through Rpostgresql to the local db, and I am able to query and write tables.

I haven't found any promising posts on updating tables with dataframe rows in R. (for my functionality this would mean adding a a new row of observations, let's say I'm updating numbers weekly).

assuming the mtcars table is uploaded as a postgres table as the following:

              postgrestable = mtcars
              postgrestable$key = rownames(postgrestable)
              postgrestable = postgrestable[, c(12, 1:2)]
              head(postgrestable)

and i have following data frame in R:

               key = c("MazdaRX4", "Toyota H5", "Chevy Delirium") 
               mpg = c(21, 22, 31) 
               cyl = c(6, 4, 6) 
               df = data.frame(key, mpg, cyl)  
               head(df)

below is the beginnings of the code:

library(RPostgreSQL)

               # create a connection
               # save the password that we can "hide" it as best as we can                      by collapsing it
                pw <- {
                "abc123"
                 }

                # loads the PostgreSQL driver
                drv <- dbDriver("PostgreSQL")
                # creates a connection to the postgres database
                # note that "con" will be used later in each connection to  the database
                 con <- dbConnect(drv, dbname = "mydb",
                 host = "localhost", port = 5432,
                 user = "postgres", password = pw)
                  rm(pw) # removes the password

                   # check for the cartable
                   dbExistsTable(con, "postgrestable")
                   #TRUE

                   #update table (not sure how to structure this)
                   sql <- "INSERT INTO postgrestable
                   VALUES ("df")"

I know the rownames are different in this case but lets assume they are the same for simplicity sake. How would I go about inserting the three rows of 'df' into the 'postgrestable'? Notice I made one repetitive because I want to take into account the fact that there might be repetitive entries. Appreciate the help guys

Answer 1

Consider running lapply() through list of sql statements:

sqllist <- paste0("INSERT INTO postgretable(key, mpg, cyl) ",
                  "VALUES('",df$key,"',",df$mpg,",",df$cyl,")")

appendAction <- lapply(sqllist, function(x) dbSendQuery(con, x))

Answer 2

Here is a (slightly adjusted) helper function from a project of mine:

insertIntoTable <- function(con, dat) {
    for (i in seq_len(nrow(dat))) {
        sql <- paste0("insert into table values('", 
                      dat[i,"symbol"], "', ",
                      dat[i,"volume"], ", ",
                      "'abc', 'def', 'ghi', ",
                      dat[i,"cost"], ", '", 
                      dat[i,"date"], "');")
        dbSendQuery(con, sql)
    }
    invisible(NULL)
}

You can first print the command string and run it by hand to test.

You can add your own logic if it is update rather than insert