I'm just starting to work with an SQLite database in R, using RSQLite and dplyr/dbplyr.
I'm looking for an overview or explanation of how SQLite data types are, or are not, related to data types in R. For example, if I have an SQLite database with a table that contains INTEGER and TEXT columns, when I read that table into R (e.g. using dbReadTable, will the data types translate such that I now have an R data frame with integer and character columns, respectively? Will SQLite NULL values become NA's in R? Is there any equivalent to R's "factor" data type in SQLite?
These are the types of questions I'm interested in, but really I just feel like I need to get a broad understanding/overview of how the SQLite/R transition will work. I apologize if some of these questions seem really basic! I have pretty extensive experience in R, but I'm very new to databases and to SQLite in particular, so if any of those questions don't make sense, it's probably because I'm misunderstanding something on the database side of things.
I have been using SQLite databases in R for a long time and really like it. The basic data types according to the website are
NULL . The value is a NULL value.
INTEGER . The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL . The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT . The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB . The value is a blob of data, stored exactly as it was input.
You set the correct data-type for a column when you create the table in the database, and R will read it accordingly.
NULL is used in the database for missing values, and R should translate them to NA. In R, the NULL value is not possible in a data frame and will always be NA.
SQLite does not know factors, so will always store strings as text. Of course you can change this back to factors when you read the table in R should you want to.
Regarding NULLs and NA's--you say that the database uses NULL for missing values, and R will translate that to NA. What about the other way around? I have some tables that I need to add to the database as text files, but of course NA gets written out as the character string "NA", which isn't understood as NULL by SQLite. What's the best way to put NULLs into the database in the first place?
Also, let me see if I'm getting the SQLite data type "translations" right. INTEGER is like R's integer, of course. REAL is like numeric, and TEXT is like character. (I read up a little more on BLOB and it doesn't look like I'll need to use that one). Does SQLite not have a logical type? I'm assuming you could just store that as TRUE and FALSE and convert back from character to logical in R, but that's a little bit of a pain. I'm definitely willing to do it--and also willing to do the same with converting "NA" to if necessary--but wanted to make sure that was the right approach before proceeding.
Oh, I might have answered my own question about NA's and NULLs. If I go through the data frame and replace all NA's with the character string "NULL" in R, will SQLite then understand that character string as a NULL, not as the string "NULL"? If so, then I think that will work.