In this blog, we are going to discuss the connection with Heroku Postgres or any other Postgres database using the language R. R is an open-source language and widely used in statistical and graphical data analysis and used by scientists and statisticians.

Without wasting your time let’s start with the steps to connect with the database.

Step 1: Install required packages. We need to install RPostgres or RPostgreSQL package to connect with Postgres. I will guide you to install both packages. You can choose anyone.

Install RPostgreSQL package using the below command in your Rstudio and any other tool you prefer.

install.packages('RPostgreSQL')

Install RPostgres package. There are two ways to install this package. One is GitHub and another one is CRAN. The difference between both the way is in CRAN you will get the latest stable version of the package and in the Github version, you will get the latest development version.

  • Install Using github. Run the below commands one after another in the terminal to install.
install.packages(‘RPostgres’)
install.packages('devtools')
install.packages(‘remotes’)
remotes::install_github("r-dbi/RPostgres")
  • Install Using CRAN. Run the below command in the terminal to install it.
install.packages("RPostgres")

Now we have installed all the required packages to work with Postgres using R.

Now we are going to show you the running sample code to connect with the database and fetch the list of tables present in the database and later on we will get the count of total records in the table.

# load package
library(DBI)

# initialise the required variable to connect with database
db <- 'databast name'  #provide the name of your db
host_db <- 'your host name of database' # name of the host
db_port <- '5432'  # or any other port specified by the DBA
db_user <- 'my_user'  
db_password <- 'my_password'

# connect with the database
con <- dbConnect(RPostgres::Postgres(), dbname = db, host=host_db, port=db_port, user=db_user, password=db_password)

Fetch the list of tables in the database mentioned in the above code block.

# List all the tables in the database specified in above connection
dbListTables(con)

Get the count of all the records in any of the table

# Simple select query to find the count of total records in training_data_20200317 table.
dbGetQuery(con, 'SELECT count(*) FROM training_data_20200317')

I hope the above example will help you in connecting with Postgres. In case you face any issues feel free to comment below.

Happy Coding!!