RPostgreSQL and sqldf basic usage

RPostgreSQL_sqldf

# www.analytikdat.cz
# www.analytikdat.cz/index.php/blog/entry/r-and-postgresql-using-rpostgresql-and-sqldf

# Load required libraries
library("RPostgreSQL")
library("sqldf")

# Establish connection
drv <- dbDriver("PostgreSQL")
# Simple version (localhost as default)
con <- dbConnect(drv)
  # Full version of connection seetting
  # con <- dbConnect(drv, dbname="dbname",host="host",port=1234,user="user",password="password", )

# We can use sqldf() to issue any command, including drop, create, select, insert
sqldf("
      /* sql comments can be used*/
      drop table if exists tmp.test_tbl;
      create table tmp.test_tbl (a int, b int);
      insert into tmp.test_tbl values (1,4);
      insert into tmp.test_tbl values (2,5);
      insert into tmp.test_tbl values (3,6);
      ",connection=con)

# We can test whether table exists (we are looking for table test_tbl in tmp schema)
dbExistsTable(con, c("tmp","test_tbl"))

# Read table from PostgreSQL into R data frame
# Note the specific syntax to access table in tmp schema.
myTable <- dbReadTable(con, c("tmp","test_tbl"))
  # Equals to myTable <- sqldf("select * from tmp.test_tbl",connection=con)

# We can switch back from using PostgreSQL driver to SQLite
# using drv="SQLite". This way, we can manipulate R data frames using 
# pure SQL.
myTable <- sqldf("select a, b, a+b as c from myTable", drv="SQLite")

# Write results back to PostgreSQL.
# overwrite=TRUE will change both data and table structure
# When row.name=TRUE then column named row.names will be added to the table
dbWriteTable(con, c("tmp","test_tbl_out"), value=myTable,overwrite=TRUE,row.names=FALSE)

# Append data to table
dbWriteTable(con, c("tmp","test_tbl_out"), value=myTable,append=TRUE, row.names=FALSE)

sqldf("select * from tmp.test_tbl_out",connection=con)

# Close PostgreSQL connection 
dbDisconnect(con)