[R-sig-DB] Postgres to R date conversion and Writing NULL to postgres database using RpgSQL issue

[Gabor Grothendieck]

On Thu, Apr 21, 2011 at 6:32 AM, subodh agrawal <subodh.iitr using gmail.com> wrote:
> Please comment on thses two queries.
>
> 1) While converting field of data type "date" selected form postgres,
> RpgSQL gives error on windows  when field value is null.

Thanks.  This reproduces the error.

library(RpgSQL)
DF <- data.frame(a = c(1, NA, 2), b = c(Sys.Date() + 1:2, NA), c =
c("a", NA, "b"))
con <- dbConnect(pgSQL(), user = "whatever", password = "whatever",
dbname = "whatever")
dbWriteTable(con, "DF", DF)

> 2) How a field can be assigned NULL value in R . I couldn't found a way to
> write NULL value in database table row using RpgSQL. I used dbWriteTable
> function to write data to database. One workaround temporarily i did is to
> write "NULL" string in database and then update "NULL" to NULL using update
> SQL script.

Continuing from above the following works in the development version
of RpgSQL (although the writeTable above is still a problem in the
development version). The NA fields are stored as NULLs and then
converted back to NAs.  (In the released version the code below only
works for character columns.)

DF2 <- DF[-2]
dbWriteTable(con, "DF2", DF2)
dbGetQuery(con, "select * from DF")

I can send you the development version if you need it now.

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com