There are two options for connecting to a PostgreSQL database:

  • Using the odbc package with a database driver
  • Using the RPostgres package

Using odbc

The odbc package, in combination with a driver, provides DBI support and an ODBC connection.

Please refer to the official PostgreSQL page for instructions on how to download and setup the driver: psqlODBC - PostgreSQL ODBC driver

Connection Settings

There are five 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)

Using RPostgreSQL

RPostgreSQL is an DBI-compliant interface to the PostgreSQL database. More information is available here: CRAN - RPostgreSQL