R connects to PostgreSQL data sources using ODBC generic connections. First, you will probably need to install something that gets the computer ready to manage ODBC connections, then you’ll have to install a specific PostgreSQL driver.

For Mac

• Follow these instructions, summarized here:

• Install homebrew if you don’t already have it

• Use homebrew to install unixODBC
  brew install unixodbc 

• Use homebrew to install psqlODBC driver
  brew install psqlodbc 
  

For Windows

• Download the official PostgreSQL ODBC driver:

• Go to downloads page

• Click on the “msi” folder

• Scroll to bottom and select newest version available
  
  Note: use x86 files for 32-bit Windows, x64 for 64-bit windows
  
  

• Download to computer

• Extract the .msi file from the .zip file (double-click and select “Extract” at the top

• Install from the .msi (important to have administrator privileges for this, I think)

• You can check whether ODBC knows about the driver by opening the ODBC Data Sources program and looking on the Drivers tab

For Mac only

• Run “odbcinst -j” in your Terminal to find the correct path the unixodbc config files - specifically the “odbcinst.ini” file
  (On my system, it was in /usr/local/etc)

• In a text editor, open odbcinst.ini and add information about the new PostgreSQL driver.

[PostgreSQL Driver]

Driver = /usr/local/lib/psqlodbcw.so

Note: the name inside the square brackets [] can be anything, but you have to remember this name to call this driver later.

Note: you should look inside /usr/local/lib make sure this is the correct path to your psqlodbc driver file.

For Mac and Windows

• In R, install and load the “DBI” and “odbc” packages
  
  

• Check for the correct name of the PostgreSQL driver by running this command:

odbc::odbcListDrivers()

The name of the driver should be the same as the name you gave the driver when you edited odbcinst.ini.

Make the connection using code like the following:

con <- DBI::dbConnect(

 odbc::odbc(),

 driver="PostgreSQL Driver",

 database="ldp",

 UID="yourusername",

 PWD="yourpassword",

 server="glintcore.net",

 port=5432,

  sslmode = "require"

)

Note: Insert the correct driver name returned from the previous command, as well as your correct login credentials.

Note: SSL should be On

Using DBI package:

• Use basic structure: dbGetQuery(connection,
  query)

• When you pass in the query, it needs to be surrounded by single quotation marks - '…query…'

• If you already have single quotation marks in your query, you can “escape them,” or keep them in your query without breaking the R code, by adding a “\” in front of them
  e.g., 'select * from table where name = \'SMITH\';'

Using Rmd files:

• If you are using Rmd files, you can create a SQL code chunk instead of an R code chunk.

• The syntax is:
  ```{sql connection = name_of_connection, output.var = ”name_for_result”}
  …normal SQL code…
  ```

The results of the methods mentioned above should be a standard R data frame. R data frames can be exported to csv using “write.csv” (base R) or “write_csv” (readr package inside the tidyverse). See the standard help documentation for syntax information.  Other output formats (e.g., Excel files) may be available using other R packages.