How to properly setup R to query local data frames from PostgreSQL?

Question

I've been using the sqldf package for quite a while using the SQLite drive, which came by default.

However, now I want to leverage PostgreSQL's window functions. I've installed RPostgresSQL and I think I'm correctly using the options sqldf's documentation specifies. But even if I try to do a simple query specifying or not the drive and dbname, an error message is returned:

library(RPostgreSQL)
library(sqldf) # the R console returns "sqldf will default to using PostgreSQL"

df = data.frame(color=c("red","blue"),value=c(10,20))

sqldf("select * from df")

sqldf("select * from df",drv="PostgreSQL",dbname=getOption("sqldf.RPostgreSQL.dbname"))

The error message returned is the following:

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

What am I missing?

@akrun I saw that too, it didn't work for me. I have PostgreSQL installed on the pc and a "test" database... anyway that solution seems really odd, why connect to a database when what you want is to query a local (in memory) data frame...? — Victor

Victor Victor · Accepted Answer · 2017-08-07T03:30:14

Ok I figure out how to make it work. Following @akrun's comment, one has to create a "test" database, however, this is not enough. You have to also setup the following options in R before running any query in sqldf (got the solution here)

options(sqldf.RPostgreSQL.user = "postgres", 
        sqldf.RPostgreSQL.password = "postgres",
        sqldf.RPostgreSQL.dbname = "test",
        sqldf.RPostgreSQL.host = "localhost", 
        sqldf.RPostgreSQL.port = 5432)

How to properly setup R to query local data frames from PostgreSQL?

1 Answers