CODEWITHSUNDEEP
rpostgresql
f.ashouri
f.ashouri

Reputation: 5667

Creating table in PostgreSQL from R platform by using RPostgreSQL

I am using RPostgreSQL package to connect the db to R. I wanted to update the db with those tables do not exist in db. Is it possible to create the new table from R in postgresql and update it with upcoming values? Any suggestion?

Sample of data:


       Date&Time               temp
1 2007-09-30 00:00:00 -0.1153333
2 2007-09-30 01:00:00 -0.4006667
3 2007-09-30 02:00:00 -0.4493333
4 2007-09-30 03:00:00 -0.7540000
5 2007-09-30 04:00:00 -0.5783333
6 2007-09-30 05:00:00 -0.3280000

Upvotes: 3

Views: 2475

Answers (3)

Daniel Lee
Daniel Lee

Reputation: 310

Here's an alternative solution that worked for me. I used RPostgreSQL along with sqldf package.

library(RPostgreSQL)
library(sqldf) 

pw <- "password"   

options(sqldf.RPostgreSQL.user = "postgres",
     sqldf.RPostgreSQL.password = pw,
     sqldf.RPostgreSQL.dbname = "some database",
     sqldf.RPostgreSQL.host = "localhost",
     sqldf.RPostgreSQL.port = 5432)

rm(pw) # removes the password

sqldf("CREATE TABLE table_name(
   user_id serial PRIMARY KEY,
   username VARCHAR(50) UNIQUE NOT NULL,
   password VARCHAR(50) NOT NULL,
   email VARCHAR(355) UNIQUE NOT NULL,
   created_on TIMESTAMP NOT NULL,
   last_login TIMESTAMP)")

Upvotes: 0

jackStinger
jackStinger

Reputation: 2055

To Create a table, you need the dbSendUpdate() function.

require(rjdbc)
...
dbSendUpdate(conn, "CREATE abc (...)")

Upvotes: 0

Dirk is no longer here
Dirk is no longer here

Reputation: 368181

We added a number of tests for automatic conversion of types to the package, see the source tarball. In particular, the conversion of SQL Datetime to POSIXct was a prime motivation for me to get the package started (as a GSoC project).

But we may simply not writing back the same way you did here. So if it complains about a missing converter for POSIXct, try converting the POSIXct column(s) to numeric, and then provide a reproducible example on the rpostgresql mailing list (see the code.google.com repo and wiki).

Upvotes: 3

Related Questions