Chapter 3 Introduction

This is a reference book on how to user PostgreSQL in Rstudio utilizing the DBI and RPostgresql packages.

Note: All queries are limited to ten rows to allow for easier reading and understanding.

After searching the internet for exstensive books I was unable to find anything to my liking. Working with databases is key to get things done in R, Rstudio, Python, and R-Shiny. So I wanted to write my own book aimed at practical knowledge on how to do things and hopefully create a good work out of the hodgepodge of junk that is out there.

First things first. You will want to ensure that you have enabled remote access to your PostgreSQL database.

How to allow remote access to PostgreSQL database:

You will need to change some configurations in the postgresql.conf file on your server.

The you will want to change the following line listen_addresses = ‘localhost’ to listen_addresses = ‘*’:

Search for it using CTRL + W

Next restart your PostgreSQL database.

You should still receive an error as you also need to configure the pg_hba.conf file as well.

Now place the followin at the very end of the file.

https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

Step 1: Install the necessary packages to check the connection.

Step 2: We want to connect to our PostgreSQL database itself. I recommend utilizing Digital Ocean to host your own cloud base PostgreSQL instance. Here is a link to a tutorial on their website to build your own if you have not done so before. Digital Ocean PostgreSQL. In the below code chunk you will want to update the repsective values with the values from your database instance.

## <PostgreSQLDriver>
  • db name will be the database you are wanting to use.
  • host will be the host your database is on. Either your localhost or the url to your database.
  • port By default the port will be 5432 for postgresql.
  • user will be the username for the database you are connecting to
  • password will be the database password you use when connecting to postgresql Next we can list the tables that are available in our database

This is the DBI way to do it in Rstudio.

## [1] "detroit"  "dodgers"  "teachers" "student"

This is the SQL syntax way to do it. Here we can see the tablename,tableowner, and the tablespace along with other housekeeping items that may be of interest to us.

Table 3.1: Displaying records 1 - 10
schemaname tablename tableowner tablespace hasindexes hasrules hastriggers rowsecurity
public detroit ben NA FALSE FALSE FALSE FALSE
pg_catalog pg_statistic postgres NA TRUE FALSE FALSE FALSE
pg_catalog pg_type postgres NA TRUE FALSE FALSE FALSE
public dodgers ben NA FALSE FALSE FALSE FALSE
pg_catalog pg_policy postgres NA TRUE FALSE FALSE FALSE
pg_catalog pg_authid postgres pg_global TRUE FALSE FALSE FALSE
public teachers ben NA FALSE FALSE FALSE FALSE
public student ben NA FALSE FALSE FALSE FALSE
pg_catalog pg_user_mapping postgres NA TRUE FALSE FALSE FALSE
pg_catalog pg_subscription postgres pg_global TRUE FALSE FALSE FALSE

Select * FROM table;

Here we are querying the entire table and bringing back all of the values.

Teachers Dataset

Table 3.2: 6 records
id first_name last_name school hire_date salary
1 Janet Smith F.D. Roosevelt HS 2011-10-30 36200
2 Lee Reynolds F.D. Roosevelt HS 1993-05-22 65000
3 Samuel Cole Myers Middle School 2005-08-01 43500
4 Samantha Bush Myers Middle School 2011-10-30 36200
5 Betty Diaz Myers Middle School 2005-08-30 43500
6 Kathleen Roush F.D. Roosevelt HS 2010-10-22 38500

Detroit Dataset

Table 3.3: Displaying records 1 - 10
Year FTP UEMP MAN LIC GR CLEAR WM NMAN GOV HE WE HOM ACC ASR
1961 320 11.0 455.5 178.50 215.98 93.4 558724 538.1 133.9 2.98 117.18 8.60 39.17 306.18
1962 320 7.0 480.2 156.41 180.48 88.5 538584 547.6 137.6 3.09 134.02 8.90 40.27 315.16
1963 320 5.2 506.1 198.02 209.57 94.4 519171 562.8 143.6 3.23 141.68 8.52 45.31 277.53
1964 320 4.3 535.8 222.10 231.67 92.0 500457 591.0 150.3 3.33 147.98 8.89 49.51 234.07
1965 320 3.5 576.0 301.92 297.65 91.0 482418 626.1 164.3 3.46 159.85 13.07 55.05 230.84
1966 320 3.2 601.7 391.22 367.62 87.4 465029 659.8 179.5 3.60 157.19 14.57 53.90 217.99
1967 320 4.1 577.3 665.56 616.54 88.3 448267 686.2 187.5 3.73 155.29 21.36 50.62 286.11
1968 320 3.9 596.9 1131.21 1029.75 86.1 432109 699.6 195.4 2.91 131.75 28.03 51.47 291.59
1969 320 3.6 613.5 837.60 786.23 79.0 416533 729.9 210.3 4.25 178.74 31.49 49.16 320.39
1970 320 7.1 569.3 794.90 713.77 73.9 401518 757.8 223.8 4.47 178.30 37.39 45.80 323.03

Student Dataset

Table 3.4: Displaying records 1 - 10
school sex age address famsize Pstatus Medu Fedu Mjob Fjob reason guardian traveltime studytime failures schoolsup famsup paid activities nursery higher internet romantic famrel freetime goout Dalc Walc health absences G1 G2 G3
GP F 18 U GT3 A 4 4 at_home teacher course mother 2 2 0 yes no no no yes yes no no 4 3 4 1 1 3 6 5 6 6
GP F 17 U GT3 T 1 1 at_home other course father 1 2 0 no yes no no no yes yes no 5 3 3 1 1 3 4 5 5 6
GP F 15 U LE3 T 1 1 at_home other other mother 1 2 3 yes no yes no yes yes yes no 4 3 2 2 3 3 10 7 8 10
GP F 15 U GT3 T 4 2 health services home mother 1 3 0 no yes yes yes yes yes yes yes 3 2 2 1 1 5 2 15 14 15
GP F 16 U GT3 T 3 3 other other home father 1 2 0 no yes yes no yes yes no no 4 3 2 1 2 5 4 6 10 10
GP M 16 U LE3 T 4 3 services other reputation mother 1 2 0 no yes yes yes yes yes yes no 5 4 2 1 2 5 10 15 15 15
GP M 16 U LE3 T 2 2 other other home mother 1 2 0 no no no no yes yes yes no 4 4 4 1 1 3 0 12 12 11
GP F 17 U GT3 A 4 4 other teacher home mother 2 2 0 yes yes no no yes yes no no 4 1 4 1 1 1 6 6 5 6
GP M 15 U LE3 A 3 2 services other home mother 1 2 0 no yes yes no yes yes yes no 4 2 2 1 1 1 0 16 18 19
GP M 15 U GT3 T 3 4 other other home mother 1 2 0 no yes yes yes yes yes yes no 5 5 1 1 1 5 0 14 15 15

Next we can list the tables that are available in our database

This is the DBI way to do it in Rstudio.

## [1] "detroit"  "dodgers"  "teachers" "student"

The following is the SQL syntax way to do it. Here we can see the tablename,tableowner, and the tablespace along with other housekeeping items that may be of interest to us.

Table 3.5: Displaying records 1 - 10
schemaname tablename tableowner tablespace hasindexes hasrules hastriggers rowsecurity
public detroit ben NA FALSE FALSE FALSE FALSE
pg_catalog pg_statistic postgres NA TRUE FALSE FALSE FALSE
pg_catalog pg_type postgres NA TRUE FALSE FALSE FALSE
public dodgers ben NA FALSE FALSE FALSE FALSE
pg_catalog pg_policy postgres NA TRUE FALSE FALSE FALSE
pg_catalog pg_authid postgres pg_global TRUE FALSE FALSE FALSE
public teachers ben NA FALSE FALSE FALSE FALSE
public student ben NA FALSE FALSE FALSE FALSE
pg_catalog pg_user_mapping postgres NA TRUE FALSE FALSE FALSE
pg_catalog pg_subscription postgres pg_global TRUE FALSE FALSE FALSE