Writing tables into a PostgreSQL database using R

A short guide to install and use RPostgreSQL and/or odbc packages.

Updated 2018-03-25

Motivation

I did run sudo apt-get on my server and R got updated. That made me unable to write PostgreSQL tables from RStudio for an hour.

Trying to fix the problem I discovered RPostgreSQL and I found it as fast as odbc database connection by using RStudio Drivers or PostgreSQL ODBC driver. This is adapted from an old post by Claudia@Work.

Besides I have many compiled R versions under opt/ and I use packrat to avoid situations like this often, I do most of my daily tasks with an R version that works out of the box if you run sudo apt-get install r-base and I just wanted to fix that.

RPostgreSQL

If you use Ubuntu first you need to go to the terminal and run:

sudo apt-get install libpq-dev

Then you can go to RStudio and run this:

install.packages("RPostgreSQL")

If the last two steps worked, you are ready to connect to your database by adapting this piece of code:

library("RPostgreSQL")
drv <- dbDriver("PostgreSQL") # choose the driver
con <- dbConnect(drv, host = "cube.yourcompany.us", user = "user", password = rstudioapi::askForPassword(), dbname = "thedatabase")

You can also specify the schema you want to write to as an optional step:

dbGetQuery(con, "SET search_path TO exampleschema")

If you have a table (i.e sales_by_store) in your workspace, then writing it into the database is straightforward:

dbWriteTable(con, "sales_by_store", sales_by_store)

Just in case the second argument from dbWriteTable() allows you to give the table a name different than the current object name, for example:

dbWriteTable(con, "sales_shopping_centers", sales_by_store)

Finally if you need to delete the recently uploaded table you can do it easily:

if(dbExistsTable(con, "sales_shopping_centers")) {dbRemoveTable(con, "sales_shopping_centers")}

obdc

If you use Ubuntu first you need to go to the terminal and run:

apt-get install odbc-postgresql

Then you can go to RStudio and run this:

install.packages("odbc")

obdc is super well documented by RStudio and one of the big advantages of it is that allows you to use RStudio “Connections” pane for easier database access. You can read more about it at Databases using R.