How to use dbGetQuery in tryCatch with PostgreSQL?

Question

I to try to use tryCatch querying my PostgreSQL database from R. Basically the query works, but I can't manage to catch errors and react to them. Here's an example

insert_rows <- function(dframe,con){

out <- list()
for(i in 1:nrow(dframe)){
query <- .... some insert statement
tryCatch({dbGetQuery(con,query)},error=function(e) print("caught"))
}

}

When I create an error e.g. by entering duplicate records to unique PKs, I do see a PostgreSQL error and warning, but it's the standard printout from RPostgreSQL. I've used tryCatch in other contexts and it always worked this way. I have used dbGetQuery alot but I can't make them work together. Also, putting tryCatch into the out list did help much.

Answer 1

Use dbSendQuery for sending insert statements. The tryCatch will catch the exception in this case:

tryCatch({
  dbSendQuery(con, "insert into wrongtable values(1, 2, 3)")
},
error = function(e) print(e)
)

For using dbGetQuery (I don't know why it fails) there is a workaround - call postgresqlExecStatement and postgresqlFetch instead of dbGetQuery:

tryCatch({
  res <- postgresqlExecStatement(con, "select * from thereisnotablewiththisname")
  postgresqlFetch(res)
},
error = function(e) print(e),
warning = function(w) print(w)
)