0

I recently asked this question.

I am able to put the whole table into the SQLite3, and am working on specifying the type. Clearly the 'r' data.frame properties imperfectly translate to SQLite3.

Question:
Using the R SQLite package, for the "dbWriteTable" is there a way to specify the type of column, particularly for boolean and date-time?

When I try writing a posixtime column using dbWriteTable:

> dbWriteTable(conn=myDBcon,name = tlist[idx],value = rt1)

then I get the following error:

Error in sqliteSendQuery(conn, statement, bind.data) : 
RAW() can only be applied to a 'raw', not a 'double'

If I treat the posixtime as a numeric, then I do not get that issue.

Community
  • 1
  • 1
EngrStudent
  • 1,924
  • 31
  • 46

1 Answers1

2

You can specify column types with the field.types argument in dbWriteTable() which takes a character vector. Specified names correspond to table names:

dbWriteTable(conn=myDBcon, name = tlist[idx], value = rt1,
             field.types=list(dte="text", val="real", bool="integer"),
             append=FALSE, overwrite=TRUE, rownames=FALSE)
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Am I looking in the wrong place in the documentation? My help documentation does not have the detailed description. I get dbWriteTable(conn,name,...). Where do I get the "..." part like you did? EDIT: aha. When I was using help it gave me "DBI" results not "RSQLite" results. Is there a way to specify the library to search, or is it better to just start with "??" before using "?"? – EngrStudent Dec 07 '15 at 21:48
  • 1
    The link in my answer points to a PDF. Navigate to table of contents and click the numbered page (p. 10) corresponding to `dbWriteTable()`. Scroll down (as function begins at bottom of page) and all listed arguments appear later (p. 11). – Parfait Dec 07 '15 at 21:58
  • Note that the only data types in SQLite are NULL, REAL, INTEGER, TEXT and BLOB and, in particular there are no date or datetime types. See https://www.sqlite.org/datatype3.html – G. Grothendieck Dec 08 '15 at 00:36
  • Also note that if the dte field is of Date class in R then it will be stored as a number (days since Epoch) despite having an SQLite type of text. If you want it stored as text you need to convert it on the R side to a character string first. – G. Grothendieck Dec 08 '15 at 01:27
  • @G.Grothendieck Good points. These variables here are example placeholders for the OP to be adjusted according to specific needs. – Parfait Dec 08 '15 at 01:58