Use Connections Pane to connect to a Postgres Database

postgres
connection

#1

Hallo,

I'm using the Connections Pane in RStudio to connect to a PostgresSQL Database.
After installing my System new (Win10, R3.5.0, RStudio1.1.453), I tried to retrace my steps to set it up again, but couldn't do so. After opening the Connections Pane the only existing sources I see are "Livy", "Spark" and "SQL Server". Postgres and all other connections are now missing.

What I tried so far is to install PostgresSQL on my Computer. I also installed the RPostgres, odbc and DBI Packages in the hope that the Postgres option would appear in the list agian, but to no end.

Does anyone know how to solve this issue? It worked before and I was able to use the full functionality of the connections pane.

Thanks for any assistance :slight_smile:


#2

Hi @Largo, the odbc package reads your installed Drivers and your existing DSN, and displays them in the Connection Pane, can you confirm that you have an ODBC driver installed for PostgreSQL?


#3

Hi, yes the odbc package is installed. Version 1.1.6.


#4

Right, but do you have the Windows PostgreSQL driver installed in your OS?


#5

Hi, yes I installed them, PostgresSQL 10. But now that you mention it, they are installed on C:/ and R and RStudio are installed on another partition D:/ of my harddrive. Could this be an issue?


#6

The driver needs to be listed in the ODBC Data Source Administrator that comes with the Windows OS: http://db.rstudio.com/best-practices/drivers/#administration


#7

Thats it! The odbc Driver was not installed when I installed PostgreSQL on my system. Here a couple of hints for others who might have the same problem:

Open RStudio and type in:

odbc::odbcListDrivers()

If postgres is not listed, you can, at least thats what I did, go tot the PostgresSQL page and download the newest Version and install it. If for whatever reason, the driver is not installed, open the

Application Stack Builder (it comes with the postgres installation)

navigate to the folder Database Drivers and select "psqlODBC 32 and/or 64". It will just do the trick!

Thanks a lot for your assistance :slight_smile: