Connecting GNU R to PostgreSQL

Question

Hello friendly Overflowers,

I have GNU R installed (the S-like statistics package; version 2.8.1) and PostgreSQL (8.4.1) but I cannot connect GNU R to my RDBMS.

When I first did this (years ago - code lost) DBI for R didn't exist. Now it does. I am also confused as to which R package to use. A quick search returns: - RPostgreSQL seems to be the most up-to-date - RPgSQL Looks abandoned. I wish they would put a date on their webpage. ;-(

My Linux distribution doesn't package R packages (irony) but I am comfortable running R CMD INSTALL package.tar.gz. I installed RPostgreSQL: a lot of documentation says to call `dbConnect' but I get the following error message: "Error: object "dbConnect" not found".

Help me Stack Overflow. You're my only hope.

Answer 1

Maybe you need to run require(RPostgreSQL) before you can use dbConnect?

Answer 2

Just for completeness, you have two more options

• RODBC which is very mature and feature-complete but doesn't correspond to the DBI framework as the PostgreSQL, MySQL, SQLite, Oracle, ... interfaces do. You also need to fiddle with ODBC setup files which can be tricky. But ODBC may be useful for other data access uses too.
• RdbiPgSQL from the BioConductor project which is also mature but uses yet another protocol that was to compete with DBI and never took of. This PostgreSQL package is featureful though.

But as a RPostgreSQL maintainer/co-author I am glad you found this one. As the other poster suggested, try library(RPostgreSQL) before issueing commands. If you encounter other problems, feel free to email me off-SO with a bug report.

Edit: There is another option of embedding R inside PostgreSQL using Joe Conway's PL/R.

Answer 3

RODBC works great for me. You just have to set up a data source name (DSN) for the database you want to connect to. I find this nice because then the specific connection info does not have to be stored in R, and it may vary for your collaborators.

Also, yes, it sounds like you haven't loaded the RPostgresSQL package.

Answer 4

My guess is you need to install the DBI package (most database packages depend on it).

If you use install.packages('RPpostgreSQL',dep=TRUE) from within R it should take care of any dependency issues.