Error when trying to connect R to PostgreSQL using RODBC

Question

I am trying to connect to R using RODBC. However, when I run

ch <- odbcConnect("rails_dev", uid="skline", pwd="d0gsleep")

I get this error:

In odbcDriverConnect("DSN=rails_dev;UID=skline;PWD=d0gsleep") :
  [RODBC] ERROR: state IM002, code 0, message [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

Any thoughts? I am new to this and at a loss for what to do next... thank you so much in advance!'

Note I am using Mac OSX

Answer 1

EDIT: The answer below is outdated, as some of the mentioned packages aren't available on CRAN any longer. I leave it here for reference.

More recent info can be found in the following question:

How to connect R with PostgreSQL on OSX 10.10.2?

---

For connection to PostgreSQL on Mac, you can use the package RpgSQL. Be sure to read the installation notes, as your PostgreSQL installation has to be set up for using JDBC. This is by far the easiest way. If you install PostgreSQL, you fire up the Stack Builder at the end of the installation process to do so (see the installation guide). Alternatively, you can use the Stack Builder (included in the postgreSQL installation) after installation to add extra components. Under Database drivers you should find postgresql JDBC.

The rdbi package mentioned in the comments, is removed from CRAN and afaik not maintained any more.

If you really need to use ODBC, you need a driver for PostgreSQL, and it has to be known to the ODBC implementation on your computer. The driver Jim M. gave you is one option, another one is the drivers of Openlinksw or the "official" one on pgFoundry. Normally, you should be able to download an ODBC driver from Stack Builder like you do for a JDBC driver : under the heading database drivers you have a postgresql ODBC as well.

For the newest versions of OSX, you need to install your own odbc manager apparently. Next to the one Eelke mentioned in the comments, you have the one from Apple as well here. In this manager, you can specify the driver location etc, and set up the connection to PostgreSQL through ODBC. How to do this, is dependent on the manager you use and is explained in the help files. It's always interesting to read the relevant section in the FAQ of iODBC and the sections on ODBC on Mac.

On a sidenote : In R, You can check which DSNs can be found by using the command odbcDataSources() of the RODBC package. If Postgresql is not mentioned in there, you know you have to look at your setup first.