Connection with R

The following code show how we can write and read data to the database:

library(RPostgreSQL)
## Loading required package: DBI
dbname <- "postgres"; dbuser <- "postgres"; dbpass <- "HG317cag#"; dbhost <- "localhost"; dbport <- 5433;
#dbhost <- "remote"
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host=dbhost, port=dbport,dbname=dbname,user=dbuser, password=dbpass)
dbExistsTable(con, "cartable")
## [1] TRUE

Write and Load Data with RPostgreSQL

# creates df, a data.frame with the necessary columns
data(mtcars)
df <- data.frame(carname = rownames(mtcars), 
                 mtcars, 
                 row.names = NULL)
df$carname <- as.character(df$carname)
rm(mtcars)

# writes df to the PostgreSQL database "postgres", table "cartable" 
dbWriteTable(con, "cartable", 
             value = df, append = TRUE, row.names = FALSE)
## [1] TRUE
# query the data from postgreSQL 
df_postgres <- dbGetQuery(con, "SELECT * from cartable")

# compares the two data.frames
identical(df, df_postgres)
## [1] FALSE
# TRUE

# Basic Graph of the Data
require(ggplot2)
## Loading required package: ggplot2
ggplot(df_postgres, aes(x = as.factor(cyl), y = mpg, fill = as.factor(cyl))) + geom_boxplot() + theme_bw()

Lastly, if we are finished, we have to disconnect from the server:

# close the connection
dbDisconnect(con)
## [1] TRUE
dbUnloadDriver(drv)
## [1] TRUE