0

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?

Victor
  • 1,163
  • 4
  • 25
  • 45
  • You may check [here](https://stackoverflow.com/questions/10237113/using-sqldf-and-rpostgresql-together) – akrun Aug 07 '17 at 02:54
  • @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 Aug 07 '17 at 03:18

1 Answers1

0

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)
Victor
  • 1,163
  • 4
  • 25
  • 45
  • Not strictly true. All options have defaults so you only have to set any options for which you want to use non-default values. In particular, the answer above shows the default values for options so if that is what you want then none of the code shown above is needed. This is documented in both ?sqldf and the README. – G. Grothendieck Aug 08 '17 at 13:48