RPostgreSQL - how to export a value from r to postgresql table

Question

my table(table1) in database

Column |  Type   | Modifiers
--------+---------+-----------
 date1  | date    | not null
 line_1 | numeric | default 0
 line_2 | numeric | default 0
 line_3 | numeric | default 0
 line_4 | numeric | default 0
 line_5 | numeric | default 0

R console

> line1 <- 32+45
> line1
[1] 77

I want export these value (line1 = 77) into the table (table1) in line_1 column and date1 column should fill with present date minus 1 day.

I tried with the below command for updating line1 value, but i am getting an error

s <- dbGetQuery(con, "insert into table1 (line_1) values (line1)")

Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: column "line1" does not exist LINE 1: insert into table1 (line_1) values (line1) ^ ) Warning message: In postgresqlQuickSQL(conn, statement, ...) : Could not create executeinsert into table1 (line_1) values (line1 )

Welcome to StackOverflow. I just formatted your question for you; learning how to do this yourself may make it more likely that other spend some time looking at the question. In general, this looks like a please program this for me question which are discouraged. — Dirk Eddelbuettel
I tried with dbwritetable(RPostgreSQL) command...but it did'nt work — Chanti
Your question is again unreadable. Learn the help system, learn to format. — Dirk Eddelbuettel

Ken Yeoh Ken Yeoh · Accepted Answer · 2015-02-03T15:54:04

I think there are two issues in your code here -

First, you probably should be using dbSendQuery() instead. This is because you are sending a command to the Postgres server.

Second, when you put text into double quotes, this usually tells R to interpret that as a string. So when you are doing "insert into table1 (line_1) values (line1)", you are actually telling Postgres to insert "line1" into "line_1". What you want here if you want to add only one value is a paste command.

Try this:

dbSendQuery(con, paste0("insert into table1 (line_1) values (",line1,")"))

And for your date:

date1<-Sys.Date()-1
dbSendQuery(con, paste0("insert into table1 (date1) values (",date1,")"))

RPostgreSQL - how to export a value from r to postgresql table

1 Answers