R and RStudio not detecting installed PostgreSQL (or any other) ODBC drivers

Question

I am trying to connect to a PostgreSQL database using the R ODBC drivers in RStudio. For some reason, the drivers are not being detected by R:

$ Rscript -e 'odbc::odbcListDrivers()'
[1] name      attribute value    
<0 rows> (or 0-length row.names)

Even though as far as I can tell, they are correctly installed (using homebrew):

$ brew list
freetds     gettext     git     icu4c       libtool     openssl     pcre2       pkg-config  
postgresql  psqlodbc    readline    sqlite      sqliteodbc  unixodbc

$ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /Users/barthf/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

$ cat /etc/odbcinst.ini
[PostgreSQL Driver]
Driver          = /usr/local/lib/psqlodbcw.so

[SQLite Driver]
Driver          = /usr/local/lib/libsqlite3odbc.dylib

$ ls /usr/local/lib/ | grep odbc.*so$
libsqlite3odbc.so
libtdsodbc.0.so
libtdsodbc.so
psqlodbca.so
psqlodbcw.so

I am running on:

R version 3.4.2 (2017-09-28)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: OS X El Capitan 10.11.6

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] DBI_1.0.0  odbc_1.1.6

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.18      dbplyr_1.1.0      compiler_3.4.2    pillar_1.2.3      later_0.7.1       plyr_1.8.4       
 [7] bindr_0.1.1       base64enc_0.1-3   tools_3.4.2       digest_0.6.12     bit_1.1-12        jsonlite_1.5     
[13] tibble_1.4.2      nlme_3.1-131      lattice_0.20-35   pkgconfig_2.0.2   rlang_0.2.2       psych_1.7.8      
[19] shiny_1.0.5       rstudioapi_0.7    parallel_3.4.2    RPostgreSQL_0.6-2 yaml_2.1.14       bindrcpp_0.2.2   
[25] stringr_1.2.0     httr_1.3.1        dplyr_0.7.5       sparklyr_0.8.4    hms_0.4.2         rprojroot_1.2    
[31] bit64_0.9-7       grid_3.4.2        tidyselect_0.2.4  glue_1.2.0        R6_2.2.2          foreign_0.8-69   
[37] tidyr_0.7.2       reshape2_1.4.2    purrr_0.2.5       blob_1.1.1        magrittr_1.5      backports_1.1.1  
[43] promises_1.0.1    htmltools_0.3.6   mnormt_1.5-5      assertthat_0.2.0  mime_0.5          xtable_1.8-2     
[49] httpuv_1.4.0      stringi_1.1.5     lazyeval_0.2.1    broom_0.4.2    

For some reason unknown to me, the RPostgreSQL::PostgreSQL() driver works fine. However I want to use the ODBC driver so I can make use of the RStudio Connections feature. When I try to use RStudio to create a New Connection the dialog window only shows options for connecting to Livy and Spark.

As suggested, here is the attempted connection code:

Using ODBC:

> con <- DBI::dbConnect(odbc::odbc(),
+                       driver = "PostgreSQL Driver",
+                       database = "postgres",
+                       UID    = rstudioapi::askForPassword("Database user"),
+                       PWD    = rstudioapi::askForPassword("Database password"),
+                       host = "localhost",
+                       port = 5432) ## returns error below
Error: nanodbc/nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib 'PostgreSQL Driver' : file not found 

Using RPostgreSQL:

con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
+   dbname = "postgres",
+   user    = rstudioapi::askForPassword("Database user"),
+   password    = rstudioapi::askForPassword("Database password"),
+   host = "localhost",
+   port = 5432) ## works fine

Any thoughts why the R ODBC drivers do not seem to work? Any suggestions on things that I can try here?

Answer 1

For anyone else still stuck on this issue - I spent a couple of hours trying to get the odbc::odbcListDrivers() command to print out the drivers I knew were on my Mac.

I normally use a windows machine and never had this issue before, so was kind of out of my depth trying to figure it out.

There are a couple of questions on StackOverflow and in the issues section of r-dbi/odbc github with various answers - but none worked for me.

I thought I’d post a detailed answer to how I solved it for my Mac.

RStudio has a guide on setting up drivers for Windows/Mac/Linux machines. After installing unixODBC and your drivers (PostgreSQL for example) you are meant to edit the following two files odbcinst.ini (defines driver options) and odbc.ini (defines connection options).

On my Mac, I didn’t know where to look so I went searching and found it at /Library/ODBC/odbcinst.ini.

This file had the driver information in it that I assumed would show up using odbc::odbcListDrivers() - but wasn't.

After a lot of googling, I found the following command to type into mac terminal odbcinst -j to (i think) show the file location of driver and data source name information. This displayed the location of the odbc.ini file at the following path /Users/myusername/.odbc.ini.

Note the leading dot in .odbc.ini.

The dot denotes hidden files. Type SHIFT + CMD + . to display them.

After navigating to /Users/myusername and displaying hidden files, I saw .odbcinst.ini also. I opened this file in a text editor and surprise, it was empty.

I opened up /Library/ODBC/odbcinst.ini, copied the contents and pasted them into /Users/myusername/.odbcinst.ini.

I jumped back into the R console and finally, I could return values using odbc::odbcListDrivers().