Using sqldf and RPostgreSQL together

djq picture djq · Apr 19, 2012 · Viewed 10.8k times · Source

When using RPostgreSQL I find that I cannot use sqldf in the same way. For example if I load the library and read in data into a data frame using the following code:

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host="localhost", user="postgres", password="xxx", dbname="yyy", port="5436")
rs <- dbSendQuery(con, "select * from table");                           
df<- fetch(rs, n = -1); dbClearResult(rs) 
dbDisconnect(con) 

I know have the contents of this table in the dataframe df. However if I want to run a SQL command using sqldf I would previously do something like this:

sqldf("SELECT * FROM df WHERE X > 10")

This no longer works as I get the error message:

Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (could not connect postgres@localhost on dbname "test"
)
Error in !dbPreExists : invalid argument type

I assume this is operator error on my part, but I can't figure how what arguments to supply to sqldf so that it just focuses on the data frame and does not try to connect to anything else.

Answer

G. Grothendieck picture G. Grothendieck · Apr 19, 2012

Using sqldf with RPostgreSQL

sqldf will automatically work with the test database in PostgreSQL if it sees that RPostgreSQL is loaded. So you can create a test database in PostgreSQL and then use sqldf with that

or, you can specify the name of a different database.

See: sqldf FAQ 12

Using sqldf with RSQLite

If you want to use sqldf with RSQLite rather than with RPostgreSQL you can use sqldf's drv argument to force it use a non-default driver. e.g.

sqldf("select foo from bar...",drv="SQLite")

or, you can set the driver globally using the "sqldf.driver" option. From within R:

options(sqldf.driver = "SQLite")

or, another possibility if you wish to use RSQLite is to detach RPostgreSQL before you use sqldf and load it again afterwards.

See ?sqldf for details.