Niels Kristian Niels Kristian - 1 month ago 6
R Question

How do I write a single row of data into a postgresql table from R?

I have a table

myschema.fruits
in a postgresql database
mydatabase
. From within an R script I would like to insert a single row to that table, at the end os my script. The table row has 3 columns
type
,
taste
and
color
. Those I have in 3 different variables in my R script with same variable names like so:

type <- "Apple"
taste <- "Sweet"
color <- "Red"


I would like to use the RPostgreSQL driver to perform this insert, but I can't figure out how to do it?

Answer

Please change host, port, user and add password if necessary.

First option: appending a data frame to the table

dt2insert = data.frame(type = "Apple",
                       taste = "Sweet",
                       color = "Red",
                       stringsAsFactors = FALSE)
con = dbConnect(dbDriver("PostgreSQL"),dbname = "mydatabase",
                host = "localhost", port = 5432,
                user = "postgres") 
dbWriteTable(con, name = c("myschema","fruits"), value = dt2insert,append=TRUE,row.names=FALSE,overwrite=FALSE)
dbDisconnect(con)

Second option: using INSERT INTO command

type <- "Apple"
taste <- "Sweet"
color <- "Red"
qry = paste0("INSERT INTO myschema.fruits VALUES ('",type,"','",taste,"','",color,"');")

con = dbConnect(dbDriver("PostgreSQL"),dbname = "mydatabase",
                host = "localhost", port = 5432,
                user = "postgres") 
dbSendQuery(con,qry)
dbDisconnect(con)