Testing Connection to Postgres Raw Data Store

Trying out access to raw data in postgres sql

• http://www.r-bloggers.com/r-and-postgresql-using-rpostgresql-and-sqldf/
• https://gist.github.com/tomasgreif/5900129
• http://stackoverflow.com/questions/17754635/is-there-a-specific-way-to-handle-timestamp-columns-in-r-when-pulling-data-using

---

Trying this on a local instance in JMCC laptop-ignore the credentials

Establish Connection

library(RPostgreSQL)

## Loading required package: DBI

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="Data_Logger",host="localhost",port=5432,
                 user="cfljam",password="admin" )

Checking if a table exists

dbExistsTable(con, c("rawdata"))

## [1] TRUE

Read in Raw Data to Data Frame

First read header and form data type list

var_names<-names(read.csv('RawData_All.dat',skip=1,header=TRUE,nrows=0))
col_types<-c(c("character","integer","character","character"),
             rep("numeric",217),rep("integer",24))

rawdata<-read.csv('RawData_All.dat',skip=4,header=FALSE,
                  colClasses=col_types,col.names=var_names,
                  na.strings=c('"NAN"'),nrows=120)

Write to a new table

if(dbExistsTable(con, "Lys_Data")){
    dbRemoveTable(con, "Lys_Data")}

## [1] TRUE

dbWriteTable(conn=con, value=rawdata, name='Lys_Data')

## [1] TRUE

Need to:

ALTER TABLE "Lys_Data"
ADD CONSTRAINT pk_timestamp PRIMARY KEY ("TIMESTAMP");

or add auto-incrementing key like so:

dbSendQuery(con,'ALTER TABLE "Lys_Data" ADD CONSTRAINT pk_timestamp PRIMARY KEY ("TIMESTAMP");')

## <PostgreSQLResult:(11897,0,16)>

Try appending some data to table

rawdata2<-read.csv('RawData_All.dat',skip=4000,header=FALSE,
                  colClasses=col_types,col.names=var_names,
                  na.strings=c('"NAN"'),nrows=120)

dbWriteTable(conn=con, value=rawdata2, name='Lys_Data',append=TRUE)

## [1] TRUE

Try Reading out whole table into a Data frame, indexing on timestamp

my_data<- dbReadTable(con,'Lys_Data',row.names="TIMESTAMP")
head(my_data[1:9,1:9])

##                     row.names RECORD LoggerName ExptName BattV
## 2014-03-04 10:05:00         1      1  LUC1_Meas  SLMACC1 13.35
## 2014-03-04 10:06:00         2      2  LUC1_Meas  SLMACC1 13.37
## 2014-03-04 10:07:00         3      3  LUC1_Meas  SLMACC1 13.41
## 2014-03-04 10:08:00         4      4  LUC1_Meas  SLMACC1 13.43
## 2014-03-04 10:09:00         5      5  LUC1_Meas  SLMACC1 13.45
## 2014-03-04 10:10:00         6      6  LUC1_Meas  SLMACC1 13.47
##                     CS650_5cm.1.1. CS650_5cm.1.2. CS650_5cm.1.3.
## 2014-03-04 10:05:00          0.423          0.088          12.48
## 2014-03-04 10:06:00          0.423          0.088          12.47
## 2014-03-04 10:07:00          0.423          0.089          12.48
## 2014-03-04 10:08:00          0.423          0.089          12.48
## 2014-03-04 10:09:00          0.423          0.088          12.48
## 2014-03-04 10:10:00          0.422          0.088          12.46
##                     CS650_5cm.1.4.
## 2014-03-04 10:05:00          27.49
## 2014-03-04 10:06:00          27.43
## 2014-03-04 10:07:00          27.41
## 2014-03-04 10:08:00          27.41
## 2014-03-04 10:09:00          27.38
## 2014-03-04 10:10:00          27.28

---

Should try this with dbWriteTable2 from caroline package which handles autoincremented primary key serial

# library(caroline)
# if(dbExistsTable(con, "Lys_Data")){
#     dbRemoveTable(con, "Lys_Data")}
# dbWriteTable2(con=con, df=rawdata2, table.name='Lys_Data',pg.update.seq = TRUE)

Disconnect

dbDisconnect(con)

## [1] TRUE