Creating table in PostgreSQL from R platform by using RPostgreSQL

Question

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

Have you looked at dbWriteTable from the package? See this question — Justin
– Justin, Commented Jan 7, 2013 at 17:35
Yes, I have tried it but I came up with this error: Error in function (classes, fdef, mtable) : unable to find an inherited method for function "dbWriteTable", for signature "PostgreSQLConnection", "character", "POSIXct" — f.ashouri
– f.ashouri, Commented Jan 7, 2013 at 17:40
if you provide some of the code you've used and the error message as well as str(yourdata) you will get a much better answer. Currently, any answer would be a shot in the dark. — Justin
– Justin, Commented Jan 7, 2013 at 17:50

Dirk is no longer here · Accepted Answer · 2013-01-07 21:13:11Z

3

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

answered Jan 7, 2013 at 21:13

Dirk is no longer here

370k60 gold badges668 silver badges742 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jackStinger · Accepted Answer · 2014-11-26 10:10:22Z

0

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

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

answered Nov 26, 2014 at 10:10

jackStinger

2,0555 gold badges23 silver badges36 bronze badges

Comments

Daniel Lee · Accepted Answer · 2017-11-30 00:57:53Z

0

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

edited Nov 30, 2017 at 0:57

answered Nov 30, 2017 at 0:49

Daniel Lee

3105 silver badges13 bronze badges

Collectives™ on Stack Overflow

Creating table in PostgreSQL from R platform by using RPostgreSQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related