Understanding PostgreSQL

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.

Understanding features of PostgreSQL

The following are the features of PostgreSQL:

  • Complex SQL queries
  • Fully ACID complaint
  • SQL subselects

We need to have installed the following prerequisites for using PostgreSQL in R:

  • Installing Postgre SQL
  • Installing RPostgre SQL

Installing PostgreSQL

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

Installing RPostgreSQL

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)
}

Exporting the data from R

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)
}
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset