PostgreSQL connection to R

Today we’ll be learning to connect to Postgre SQL like Amazon Redshift to R. So the package available in R is RPostgreSQL.

Install and load the package in R

install.packages("RPostgreSQL")
library(RPostgreSQL)

Connection between R to PostgreSQL and load the PostgreSQL driver

drv <- dbDriver("PostgreSQL")
conn <-dbConnect(drv,host='host',port='1234',dbname='db',user='user-name',
password='password')

Get list of all the tables in database

dbListTables(conn)

Query table as the in SQL

dbSendQuery(conn,"select * from table_name")

Read table in R

table_name<- dbReadTable(conn, "tablen_name")

Get the summary of database/connection

summary(conn)

To close the current query

dbClearResult(dbListResults(conn)[[1]])

To disconnect database

dbDisconnect(conn)

 

 

This was the basic intro of RPostgreSQL package in R. I’ll add more as I get more out of it.

Keep Visiting for more Analytics tutorials.