I've followed the documentation on db.rstudio.com and other sites, and I'm not able to connect to my Postgres database from Rstudio. I'm on macos 10.14. Running:
con <- DBI::dbConnect(odbc::odbc(), Driver = "PostgreSQL Driver", Database = "mydatabase", UID = "myuser", PWD = "mypassword", Host = "localhost", Port = 5432)
Gives the error:
Error: nanodbc/nanodbc.cpp:983: 00000:
At first, I got an empty list when running
odbcListDrivers(), however I was able to get it to show the postgres driver location/value by copying
odbcinst.ini from the global
/etc to my user (
cp /etc/odbcinst.ini ~/.odbcinst.ini).
odbcListDataSources() returned nothing until I did the same thing (
cp /etc/odbc.ini ~/.odbc.ini)
odbcinst.ini are locked from editing via Finder, but can be edited via nano/terminal.
I also tried to solve the problem by installing the database drivers via Postgres' stack builder. This has left a file called
[PostgreSQL/12] InstallationDirectory=/Library/PostgreSQL/12 Version=12.1-3 Shortcuts=1 DataDirectory=/Library/PostgreSQL/12/data Port=5432 ServiceID=postgresql-12 Locale=C Superuser=postgres Serviceaccount=postgres Description=PostgreSQL 12 Branding=PostgreSQL 12 SB_Version=4.1.0 pgAdmin_Version=4.16 CLT_Version=12.1.3 [psqlODBC] Description=ODBC drivers for PostgreSQL Server, packaged by EnterpriseDB. InstallationDirectory=/Library/PostgreSQL/psqlODBC Version=12.01.0000-1 Branding=Postgres Plus Add-ons
I tried using the
InstallationDirectory, specified in this file, in the
odbc.ini file, but that didn't change anything.
Overall I'm suspecting that it's a permissions issue, but I'm not quite sure where to look next.
Thanks for your help!