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

Question

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?

score 3 · Answer 1 · answered Oct 25 '16 at 03:03

As an alternative method using the INSERT INTO command, consider using the low level postgresqlExecStatement function, which allows parametrization of the query. The primary advantage of this is that you don't have to manually construct the query string for the appropriate data types, in this case you can leave out the extra quotation marks ':

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

con = dbConnect(dbDriver("PostgreSQL"),dbname = "mydatabase",
                host = "localhost", port = 5432,
                user = "postgres") 
tmp <- postgresqlExecStatement(con,
               'insert into myschema.fruits VALUES ($1, $2, $3)',
               list(type, taste, color))
dbClearResult(tmp)
dbDisconnect(con)

score 2 · Accepted Answer · answered Oct 06 '16 at 09:38

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)

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

2 Answers2