Is it possible to read a data table from PostgreSQL?

Question

I am analyzing a large amount of data stored in a PostgreSQL database. For speed and memory, I use the data.table package. I am currently doing this to read data.

 library(RPostgreSQL) library(data.table) ... query <- "SELECT * FROM eqtl" data <- as.data.table(dbGetQuery(con, query)) 

I am wondering if there is a better way to do this that does not include reading all of this in data.frame and then copying it to data.table .

Answer 1

As Arun noted in a comment, you can simply use the results of setDT on dbGetQuery .

In addition, my package has a dwtools helper function that extends this function to automatically setkey if necessary. This was useful for tidying up. It also integrates the interface with other database providers, so you can link data.table using different databases.
A simple use of the selection would look like this:

 my_dt = db("SELECT * FROM eqtl") # to setkey use db("SELECT * FROM eqtl", key="mykeycol") 

Heavily extended example from the packaging guide:

 jj_aggr = quote(list(amount=sum(amount), value=sum(value))) r <- db("sales",key="geog_code" # read fact table from db )[,eval(jj_aggr),keyby=c("geog_code","time_code") # aggr by geog_code and time_code ][,db(.SD) # write to db, auto.table.name ][,db("geography",key="geog_code" # read lookup geography dim from db )[.SD # left join geography ][,eval(jj_aggr), keyby=c("time_code","geog_region_name")] # aggr ][,db(.SD) # write to db, auto.table.name ][,db("time",key="time_code" # read lookup time dim from db )[.SD # left join time ][, eval(jj_aggr), keyby=c("geog_region_name","time_month_code","time_month_name")] # aggr ][,db(.SD) # write to db, auto.table.name ] 

It will read data from several databases, combines, combines, saves intermediate results into several databases.