I have an issue with a mixed type column in a database using RSQLite. When I query the database using dbGetQuery, it seems to coerce strings to numeric producing NAs. It gives the following warning message: “mixed type, first seen values of type real, coercing other values of type string”. The column has been declared as VARCHAR. I tried to use CAST AS as in dbGetQuery(con, ‘SELECT CAST(problematic_column_name AS TEXT) FROM tbl’)
but that didn’t do the trick. Am I using CAST AS incorrectly or is there another way of forcing dbGetQuery to convert the column to text? Or should I be using an entirely differnt approach for fixing this issue?
Update: I pass the result of my original query to an R data frame as in:
result <- dbGetQuery(con, ‘SELECT problematic_column_name FROM tbl’)`
The idea to use CAST AS came from this post: stackoverflow.com/q/64655954/2554330. But when I pass dbGetQuery(con, ‘SELECT CAST(problematic_column_name AS TEXT) FROM tbl’), I still get the same issue with NAs being rendered although noo warning message is displayed.