rpostgreSQL: Overwrite the data based on the time

Question

Our finance team is using shiny app to upload the csv file to postegreSQL monthly. Sometimes, they need to revise the data and then upload again.

Let's have some example to make the problem easier to understand:

# Retrieve data from PostgreSQL
>monthly_expense <- dbGetQuery(con, "SELECT * from expense_table2")
>monthly_expense
>month   type  USD
 201605    A   200
 201605    B   300
 201606    A   105
 201606    B   200

 # Produce new 201606 data
 >month<-c("201606", "201606")
 >type<-c("A", "B")
 >USD<-c(150, 250)
 >new_data<-data.frame(month, type, USD )
 >new_data
    month    type USD
 1  201606    A   150
 2  201606    B   250

So how can I replace 201606 data with new one? I think the following command should be revised in order to specify that the data of 2016 should be overwrite:

 dbWriteTable(con, "expense_table2", value = new_data, append=T, overwrite = TRUE)  

Answer 1

Consider an update query using dbSendQuery:

sqlstrings <- paste0("UPDATE expense_table2 SET USD = ", newdata$USD , " 
                      WHERE month='", newdata$month, "' AND type='", newdata$type, "';")

queryruns <- lapply(sqlstrings, function(x) dbSendQuery(con, x))

Alternatively, update existing dataframe and then overwrite Postgres' db table with dbWriteTable:

index <- match(monthly_expense$month, new_data2$month)
monthly_expense[index, 2:3] <- new_data2[2:3]

dbWriteTable(con, "expense_table2", value = monthly_expense, append=T, overwrite = TRUE) 

Still more, rbind the non-updated rows to newdata and push that to database:

newdata <- rbind(monthly_expense[!(monthly_expense$month %in% newdata$month),],
                 newdata)

dbWriteTable(con, "expense_table2", value = new_data, append=T, overwrite = TRUE)