Creating new SQL table from dplyr object without using R memory

Question

I'm using dplyr to do manipulations with a large tables in PostgreSQL. After multiple manipulations I have large derived table which I want to save to a new table. The crude solution is to load it into R memory and write to database. This sounds horrible, as the new table is just a CREATE + SQL, produced by dplyr. Is there any way how I could apply CREATE or UPDATE to database dplyr object using build-in functions?

Answer 1

Hoping to gain a few points by converting Hadley's helpful comment into an answer. Also, I think there may have been some changes that allow copy_to to do this as well (at least it appeared to work for me).

data(iris)
remoteDb <- src_postgres(dbname="irisDb",host="remoteDB.somewhere.com",
                          port=5432,user="yourUser",password="yourPass")

irisSql <- copy_to(remoteDb,iris,"iris_table",temporary=FALSE)

irsSqlPermanent <- compute(irisSql, name="iris_table_permanent", temporary=FALSE) 

The first two lines grab the R standard dataset "iris" and set up a connection (to Postgres in this case).

The copy_to line takes advantage of what appears to be an undocumented argument temporary that allows the data frame to persist in the database (found that one in a bug report). The compute line also works as intended, but I'm not sure it is needed if you use temporary=FALSE with copy_to.