The following information is taken from https://www.r-bloggers.com/2015/05/getting-started-with-postgresql-in-r/.
You can get the opensource PostgreSQL from https://www.postgresql.org/download/. PostgreSQL costs nothing and is available for Windows, Mac OS X, Linux, etc. Installation instructions are available on the site. When installing, be sure to put your password somewhere safe as it will be needed in the future.
You will need to create a user and a demonstration database. Instructions for how to do this can be found in pgAdmin_4_Hints.docx.
Using pgAdmin we can create a little demo database—maybe demoCarsDatabase. Add a table with a name that you can remember, like demoCarsTable. Use the dropdown to select a user. You can add a description if it makes you feel better.
Click on the “Security” tab. Under “Privileges” add a line. Using the dropdowns, select your user as the “Grantee” with “Privileges” giving at least the ability to create a database. The “Grantor” is going to be the administrative superuser, postgres.
To see the SQL code that has been generated click on the “SQL” tab.
Now, click on the “Save” button. Your new table should appear under “Tables” and the counter should increase by one.
It seems like we can use either RPostgres or RPostgreSQL to connect with the PostgreSQL database. We will attempt using RPostgres first.
The first step is to install and load the RPostgres package.
p_load(RPostgres)
### Loading by hand
#install.packages('RPostgreSQL') ### Install the package. Assumes internet connection.
#library(RPostgreSQL)
### Loading using pacman
#p_load(RPostgreSQL) #library(DBI)
db <- 'demoCarsDatabase' #provide the name of your db. 'postgres' is typical.
host_db <- 'localhost' #i.e. 'ec2-54-83-201-96.compute-1.amazonaws.com'
db_port <- '5432' # or any other port specified by the DBA. 5432 is typical.
db_user <- rstudioapi::askForPassword("Database User Name ['postgres' is typical]:")
# 'postgres' is created at installation.
# For personal demo use 'user'
#db_user <- 'user'
#db_user <- 'postgres'
db_password <- rstudioapi::askForPassword("Database Password:") # password for the db_user
# For personal demo use 'notSecure'
#db_password <- 'notSecure'
#db_password <- 'xxxxxxxxxxx'
con <- dbConnect(RPostgres::Postgres(),
dbname = db,
host = host_db,
port = db_port,
user = db_user,
password = db_password
) Check for existence of demoCarsDatabase database and look for existing tables.
dbListTables(con)## character(0)Create demomtcars and democars tables.
### Write the internal mtcars dataframe to the demomtcars table
dbWriteTable(con, "demomtcars", mtcars, overwrite = TRUE)
### Check to see if the table exists.
dbListTables(con)## [1] "demomtcars" ### Alternate method for writing used to create the democars table
data('mtcars')
my_data <- data.frame(carname = rownames(mtcars), mtcars, row.names = NULL)
my_data$carname <- as.character(my_data$carname)
rm(mtcars)
dbWriteTable(con, name='democars', value=my_data, overwrite = TRUE)
### And check to see if the table exists
dbListTables(con)## [1] "demomtcars" "democars"We can read the tables.
### Read the table we just wrote without assignment
dbReadTable(con, "democars")## carname mpg cyl disp hp drat wt qsec vs am gear carb
## 1 Mazda RX4 21.0 6 160.0 110 3.90 2.620 16.46 0 1 4 4
## 2 Mazda RX4 Wag 21.0 6 160.0 110 3.90 2.875 17.02 0 1 4 4
## 3 Datsun 710 22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1
## 4 Hornet 4 Drive 21.4 6 258.0 110 3.08 3.215 19.44 1 0 3 1
## 5 Hornet Sportabout 18.7 8 360.0 175 3.15 3.440 17.02 0 0 3 2
## 6 Valiant 18.1 6 225.0 105 2.76 3.460 20.22 1 0 3 1
## 7 Duster 360 14.3 8 360.0 245 3.21 3.570 15.84 0 0 3 4
## 8 Merc 240D 24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2
## 9 Merc 230 22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2
## 10 Merc 280 19.2 6 167.6 123 3.92 3.440 18.30 1 0 4 4
## 11 Merc 280C 17.8 6 167.6 123 3.92 3.440 18.90 1 0 4 4
## 12 Merc 450SE 16.4 8 275.8 180 3.07 4.070 17.40 0 0 3 3
## 13 Merc 450SL 17.3 8 275.8 180 3.07 3.730 17.60 0 0 3 3
## 14 Merc 450SLC 15.2 8 275.8 180 3.07 3.780 18.00 0 0 3 3
## 15 Cadillac Fleetwood 10.4 8 472.0 205 2.93 5.250 17.98 0 0 3 4
## 16 Lincoln Continental 10.4 8 460.0 215 3.00 5.424 17.82 0 0 3 4
## 17 Chrysler Imperial 14.7 8 440.0 230 3.23 5.345 17.42 0 0 3 4
## 18 Fiat 128 32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1
## 19 Honda Civic 30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2
## 20 Toyota Corolla 33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1
## 21 Toyota Corona 21.5 4 120.1 97 3.70 2.465 20.01 1 0 3 1
## 22 Dodge Challenger 15.5 8 318.0 150 2.76 3.520 16.87 0 0 3 2
## 23 AMC Javelin 15.2 8 304.0 150 3.15 3.435 17.30 0 0 3 2
## 24 Camaro Z28 13.3 8 350.0 245 3.73 3.840 15.41 0 0 3 4
## 25 Pontiac Firebird 19.2 8 400.0 175 3.08 3.845 17.05 0 0 3 2
## 26 Fiat X1-9 27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1
## 27 Porsche 914-2 26.0 4 120.3 91 4.43 2.140 16.70 0 1 5 2
## 28 Lotus Europa 30.4 4 95.1 113 3.77 1.513 16.90 1 1 5 2
## 29 Ford Pantera L 15.8 8 351.0 264 4.22 3.170 14.50 0 1 5 4
## 30 Ferrari Dino 19.7 6 145.0 175 3.62 2.770 15.50 0 1 5 6
## 31 Maserati Bora 15.0 8 301.0 335 3.54 3.570 14.60 0 1 5 8
## 32 Volvo 142E 21.4 4 121.0 109 4.11 2.780 18.60 1 1 4 2 ### Or, with assignment
df_democars <- dbReadTable(con, "democars")
head(df_democars)## carname mpg cyl disp hp drat wt qsec vs am gear carb
## 1 Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
## 2 Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
## 3 Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
## 4 Hornet 4 Drive 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
## 5 Hornet Sportabout 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
## 6 Valiant 18.1 6 225 105 2.76 3.460 20.22 1 0 3 1 rm(df_democars)Set up carname as a primary key.
dbGetQuery(con, 'ALTER TABLE democars ADD CONSTRAINT cars_pk PRIMARY KEY ("carname")')## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries## data frame with 0 columns and 0 rowsMake a query.
cars_query <- dbSendQuery(con,
'SELECT carname, cyl, gear FROM democars WHERE cyl >= 5 AND gear >=4'
)
fetch_Result <- dbFetch(cars_query)
head(data.frame(fetch_Result))## carname cyl gear
## 1 Mazda RX4 6 4
## 2 Mazda RX4 Wag 6 4
## 3 Merc 280 6 4
## 4 Merc 280C 6 4
## 5 Ford Pantera L 8 5
## 6 Ferrari Dino 6 5 dbClearResult(cars_query)
dbWriteTable(con, 'newtable', fetch_Result)
dbReadTable(con, 'newtable')## carname cyl gear
## 1 Mazda RX4 6 4
## 2 Mazda RX4 Wag 6 4
## 3 Merc 280 6 4
## 4 Merc 280C 6 4
## 5 Ford Pantera L 8 5
## 6 Ferrari Dino 6 5
## 7 Maserati Bora 8 5Clean up and break the connection.
dbListTables(con)## [1] "demomtcars" "democars" "newtable" dbRemoveTable(con, 'demoCarsTable')## Error: Failed to fetch row: ERROR: table "demoCarsTable" does not exist dbRemoveTable(con, 'demomtcars')
dbRemoveTable(con, 'democars')
dbRemoveTable(con, 'newtable')
dbListTables(con)## character(0) dbDisconnect(con) If we now try to use the connection, it will fail.
dbListTables(con)## Error: external pointer is not valid