2

I'm trying to write an R tibble object (or data.frame) into a Postgres database using the RPostgres package. Following the documentation, I'm trying to use the dbWriteTable function.

However, I realized that there's no specification of field.types in the method. Here I provide a minimal example where you can see the method failing when the input has a POSIXct timestamp (and passing when the timestamp is interpreted as a character). 

library(dplyr)
library(DBI)
library(RPostgres)
library(tibble)

conn <- dbConnect(RPostgres::Postgres(),
  host = url$host,
  port = url$port,
  dbname = url$dbname,
  user = url$user,
  password = url$password
)

test <- tibble(
  words = c("hallo","ja", "nein"),
  value = c(3,4,5),
  time= as.POSIXct(c("2016-05-04 04:32","2016-06-02 09:37","2016-07-12 10:43")))
test2 <- tibble(
  words = c("hallo","ja", "nein"),
  value = c(3,4,5),
  time= c("2016-05-04 04:32","2016-06-02 09:37","2016-07-12 10:43"))

dbWriteTable(conn = conn, name = "words", value = test)
> Error in eval(substitute(expr), envir, enclos) :
> ERROR:  invalid input syntax for type real: "2016-05-04 04:32:00"
> CONTEXT:  COPY words, line 1, column time: "2016-05-04 04:32:00"

dbWriteTable(conn = conn, name = "words2", value = test2)
> [1] TRUE

Does anyone know of a better way to write a table with timestamps in Postgres using R?

S-Man
  • 22,521
  • 7
  • 40
  • 63
spsaaibi
  • 452
  • 4
  • 13

1 Answers1

0

Using PostgreSQL driver in the RPostgreSQL package works. Also, the format of the third timestamp does not match default for as.POSIXct - too many digits for hour. So I changed "2016-07-12 010:43" to "2016-07-12 10:43" and you should find it works fine.

library(dplyr)
library(DBI)
library(RPostgreSQL)
library(tibble)

#connect to db    
url <- list(user="postgres",password="postgres",dbname="test")

conn <- dbConnect(drv="PostgreSQL", user=url$user, password=url$password, dbname = url$dbname)


test <- tibble(
  words = c("hallo","ja", "nein"),
  value = c(3,4,5),
  time= as.POSIXct(c("2016-05-04 04:32","2016-06-02 09:37","2016-07-12 10:43")))
test2 <- tibble(
  words = c("hallo","ja", "nein"),
  value = c(3,4,5),
  time= c("2016-05-04 04:32","2016-06-02 09:37","2016-07-12 010:43"))


dbWriteTable(conn = conn, name = "words", value = test)

[1] TRUE

dbWriteTable(conn = conn, name = "words2", value = test2)

[1] TRUE

dbDisconnect(conn)
Chris Holbrook
  • 2,531
  • 1
  • 17
  • 30
  • Hi, Thanks for your comment Chris. I fixed the typo in the original example, which unfortunately doesn't solve the problem. – spsaaibi Oct 24 '16 at 13:24
  • You might try PostgreSQL driver in the RPostgreSQL package. Your example works fine for me. I can edit my post if it would help. – Chris Holbrook Oct 24 '16 at 13:27
  • This made me re-visit `RPostgreSQL`, which solved some ssl connection problems (which weren't even described in this post). Thanks Chris! – spsaaibi Oct 24 '16 at 18:48