There are two options for connecting to a PostgreSQL database:
- Using the
odbcpackage with a database driver
- Using the
odbc package, in combination with a driver, provides
DBI support and an ODBC connection.
PostgreSQL - The official PostgreSQL website provides instructions on how to download and setup their driver: psqlODBC - PostgreSQL ODBC driver
RStudio Professional Drivers - RStudio Server Pro, RStudio Connect, or Shiny Server Pro users can download and use RStudio Professional Drivers at no additional charge. These drivers include an ODBC connector for PostgreSQL databases. RStudio delivers standards-based, supported, professional ODBC drivers. Use RStudio Professional Drivers when you run R or Shiny with your production systems. See the RStudio Professional Drivers for more information.
There are six settings needed to make a connection:
- Driver - See the Drivers section for setup information
- Server - A network path to the database server
- Database - The name of the schema
- UID - The user’s network ID or server local account
- PWD - The account’s password
- Port - Should be set to 5432
con <- DBI::dbConnect(odbc::odbc(), Driver = "[your driver's name]", Server = "[your server's path]", Database = "[your database's name]", UID = rstudioapi::askForPassword("Database user"), PWD = rstudioapi::askForPassword("Database password") Port = 5432)
RPostgreSQL is an DBI-compliant interface to the PostgreSQL database. The package acts as both the database driver and the DBI interface.
The code, and additional information, are also accessible via the Google Code project page. The official CRAN page is here: RPostgreSQL: R Interface to the ‘PostgreSQL’ Database System