0

Hi I am trying to update a postgresql table using RpostgreSQL package, the commands in R are executed successfully but the new data is not getting reflected in the database. Below are the commands i have executed in R

for(i in new_data$FIPS) {
  drv <- dbDriver("PostgreSQL")
  con <- dbConnect(drv, dbname="ip_platform", host="******", port="5432", user="data_loader", password="******")
  txt <- paste("UPDATE adminvectors.county SET attributes= hstore('usco@TP-TotPop@2010'::TEXT,",new_data$TP.TotPop[new_data$FIPS == i],"::TEXT) where geoid ='",i,"'")
  dbGetQuery(con, txt)
  dbCommit(con)
  dbDisconnect(con)
}

Can anyone let me know if I have done something wrong? Any help is highly appreciated

Ravi Alluru
  • 1
  • 1
  • 2
    Not sure if it will change anything but you probably want to use `dbSendQuery` instead of `dbGetQuery` because you aren't actually pulling anything. Also, you need to commit before disconnecting. – cdeterman Oct 30 '15 at 16:05
  • Well i have tried dbSendQuery as well but still having the same problem. For each iteration the dbCommit is returning a warning saying "There is no transaction in progress" – Ravi Alluru Oct 30 '15 at 16:28
  • 1
    @sckott Major groan. What exactly is your issue with RPostgreSQL which actually *has been on CRAN* since 2008? – Dirk Eddelbuettel Oct 30 '15 at 16:37

3 Answers3

0

You are calling dbGetQuery instead of dbSendQuery and also disconnecting from the database in your for loop. You also are creating a new connection object for every loop iteration, which is not necessary. Try this:

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="ip_platform", host="******", port="5432", user="data_loader", password="******")

for(i in new_data$FIPS) {
  txt <- paste("UPDATE adminvectors.county SET attributes= hstore('usco@TP-TotPop@2010'::TEXT,",new_data$TP.TotPop[new_data$FIPS == i],"::TEXT) where geoid ='",i,"'")
  dbSendQuery(con, txt)
}

dbDisconnect(con)

You shouldn't call dbCommit(con) explicitly. The enclosing transaction will always be commited when dbSendQuery returns, exactly as when you do an UPDATE with pure SQL. You don't call COMMIT unless you have created a new transaction with BEGIN TRANSACTION.

The warning "there is no transaction in progress" is PostgreSQL's way of telling you that you have issued a COMMIT statement without having issued a BEGIN TRANSACTION statement which is exactly what you are doing in your function.

kliron
  • 4,383
  • 4
  • 31
  • 47
  • The maximum number of connections were exceeding when i disconnect the connection after all the iterations, that is the reason i was trying to establish and disconnect the connection for each iteration of the for loop – Ravi Alluru Oct 30 '15 at 17:30
0

Simplify, simplify, simplify -- the RPostgreSQL has had unit tests for these types of operations since the very beginning (in 2008 no less) and this works (unless you have database setup issues).

See eg here in the GitHub repo for all the tests.

Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725
0

Thanks for all your inputs. The issue is with the paste() function which I used in the for loop. The paste() function has replaced the comma with a space in the query as a result the where condition is failing. I have added a sep="" attribute in the paste() and the query is now properly sent to the database and the rows are getting updated as expected.

Ravi Alluru
  • 1
  • 1