Writing tables into a PostgreSQL database using R

If you are using a PostgreSQL database to store your data and R to process it, then you may want to access and edit your DB directly from R.

This is possible using a package called “RPostgreSQL” available from CRAN.

# Start R

 > R

# Import the library (I assume the library is already installed)

 > library("RPostgreSQL")

# Choose the driver

 > drv <- dbDriver("PostgreSQL")

# Connect to your database (I assume the database already exists on the localhost)

 > con <- dbConnect(drv, host="localhost", user= "exampleuser", password="examplepassword", dbname="exampledb")

# Specify the schema you want to write to (optional, the public shema is the default one)

 > dbGetQuery(con, "SET search_path TO exampleschema")

# Load the table from a csv (1) or an existing R archive (2):
# 1

 > x <- data.frame(read.table("/home/user/table.csv",sep=",",header=TRUE))

# or
# 2

 > load("/home/user/table.rda")
 > x <- data.frame(table)

# Delete any existing table with the same name:

 > if(dbExistsTable(con,"table1")) {dbRemoveTable(con,"table1")}

# Finally write a new table:

 > dbWriteTable(con,"table1", x)

More details here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s