3

I have a data frame which I need to write as a temp table in R using RPostgreSQL package.

Example:

>data(USArrests)
#Assuming that we have already established a connection to the postgres db
#Let conn be the postgres connection object
>dbWriteTable(conn, "temp_table_data", USArrests, temp.table=TRUE)

This does not work. The argument temp.table is seemingly ignored.

If there is no way out to deal with dbWriteTable, is there anyway to write a data frame as a temp table ?

Improve this question
4
  • 1
    Where did you get the idea that temp.table=TRUE might even work? Commented Oct 9, 2012 at 10:45
  • Probably from this feature request. It notes, however, that with that driver, you shouldn't try and do this. Commented Oct 9, 2012 at 19:55
  • 1
    If your underlying purpose here is just to apply a PostgreSQL sql statement to an R data frame then you could try sqldf. For example this counts the rows in USArrests: library(RPostgreSQL); library(sqldf); sqldf('select count(*) from "USArrests"') . See sqldf.googlecode.com and ?sqldf . Commented Oct 10, 2012 at 2:48
  • @Grothendieck: Thanks for the pointer. Much appreciated. The road block which I'm facing currently is that sqldf is not available for R version 2.13.0. Thanks anyways Commented Oct 10, 2012 at 6:00

2 Answers 2

Reset to default
7

you can use the argument is.temp = TRUE and it works

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The simplest approach I can think of would be to return the data frame as a result set, and write it to a temporary table using a wrapping function written in plpgsql. This means that you would have to break up the logic into a calculation and a storage layer.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.