CODEWITHSUNDEEP
rpostgresqlrpostgresql
Niels Kristian
Niels Kristian

Reputation: 8865

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?

Upvotes: 1

Views: 1595

Answers (2)

Alex
Alex

Reputation: 15738

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)

Upvotes: 3

Gabriel Mota
Gabriel Mota

Reputation: 312

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)

Upvotes: 2

Related Questions