PostgreSQL is an open source object relational database management system. PostgreSQL runs on most of the operating systems such as Linux, UNIX, and Windows. It supports text, image, sound, and video data sources. It supports programming technologies such as C, C++, Java, Python, Ruby, and Tcl.
The following are the features of PostgreSQL:
We need to have installed the following prerequisites for using PostgreSQL in R:
In this section, we will learn about installing PostgreSQL.
The given commands will be followed for the installation of PostgreSQL:
// updating the packages list Sudo apt-get update // installing postgresql sudo apt-get install postgresql postgresql-contrib // creating postgresql user su – postgres createuser
We will now see how to install and use RPostgreSQL:
# installing package from CRAN install.packages(RPostgreSQL) Importing the data into R# loading the installed package library(RPostgreSQL) ## load the PostgreSQL driver drv <- dbDriver("PostgreSQL") ## Open a connection con <- dbConnect(drv, dbname="oxford") ## Submits a statement rs <- dbSendQuery(con, "select * from student") ## fetch all elements from the result set fetch(rs,n=-1) ## Closes the connection dbDisconnect(con) ## Frees all the resources on the driver dbUnloadDriver(drv)
With the following code, we will learn how to operate data stored at PostgreSQL from within R:
opendbGetQuery(con, "BEGIN TRANSACTION") rs <- dbSendQuery(con, "Delete * from sales as p where p.cost>10") if(dbGetInfo(rs, what = "rowsAffected") > 250){ warning("Rolling back transaction") dbRollback(con) }else{ dbCommit(con) }
In this section, we are going to learn how to load data, write the contents of the dataframe value into the table name specified, and remove the specified table from the database connection:
conn <- dbConnect("PostgreSQL", dbname = "wireless") if(dbExistsTable(con, "frame_fuel")){ dbRemoveTable(conn, "frame_fuel") dbWriteTable(conn, "frame_fuel", fuel.frame) } if(dbExistsTable(conn, "RESULTS")){ dbWriteTable(conn, "RESULTS", results2000, append = T) else dbWriteTable(conn, "RESULTS", results2000) }