Let's say I have mtcars in a postgres server.
library("RODBC")
library("odbc")
library("RPostgres")
con = dbConnect(RPostgres::Postgres(),dbname="example_db",port = 5432,user = "postgres", password = "db_password")
I can update the mtcars table with:
good_old_mtcars = data.frame(cbind("ids" = seq(1:nrow(mtcars)), mtcars))
dbWriteTable(con1, "example_db_mtcars", good_old_mtcars, temporary = FALSE, append=FALSE, overwrite=TRUE)
It happens that some columns may need updating. Eg. updating qsec
with some calculation
good_old_mtcars$qsec = good_old_mtcars$qsec * sqrt(good_old_mtcars$wt)
How could I update only the qsec
column without deleting and uploading the whole table again?
** mtcars is a parent table and the ids
variable is used by other tables so re-uploading the whole data frame breaks other things in the database :(
There is a solution here referenced here but it is 15 years old. Any better way to do it?
You can use
rows_update()
with thein_place
param set toTRUE
, like this: