Coerce dbGetQuery to produce strings

Question

I have a SQL database with two tables. In one all of the data is stored as TEXT, while the other stores columns as TEXT, DOUBLE, TIME, INT, DATE, etc. (I checked using dbDataType(ANSI(), data)) When I try to join the two in R, though, I get the following warning message:

Warning messages:
1: In result_fetch(res@ptr, n = n) : Column `7.Preferred.Food`: mixed type, first seen values of type integer, coercing other values of type string

When I check the structure of the output dataframe, column 7.Preferred.Food is an integer. The problem is, I want my data imported as a string. Is there a way to compel dbGetQuery to produce only characters? My code is below:

breed= dbGetQuery(con, 'select * from foster
                     inner join pets
                     on `ID` = `TAG`')

Preferred Food is an integer in pets but not in foster, but switching the order has no effect.

I've tried:

dbExecute(con, "ALTER TABLE file ALTER COLUMN 7.Preferred.Food TEXT")

and got

Error in result_create(conn@ptr, statement) : near "ALTER": syntax error

and

update_query <- paste("update pets",
                      "ALTER COLUMN 7.Preferred.Food TEXT")
dbSendQuery(con, update_query)

and got

Error in result_create(conn@ptr, statement) : near "ALTER": syntax error

Edit: For work restrictions, I'm stuck with RSQLite and DBI (and the tidyverse); I can't use the whole plethora of extra SQL tools in R.

score 0 · Answer 1 · answered Nov 03 '20 at 20:34

SQLite is very specific, one column in a table can contain values of mixed type. The example below recreates result sets with mixed types:

library(DBI)

con <- dbConnect(RSQLite::SQLite())
dbGetQuery(con, "SELECT 1 AS a, 1 AS id UNION SELECT 'q', 2 ORDER BY id")
#> Warning in result_fetch(res@ptr, n = n): Column `a`: mixed type, first seen
#> values of type integer, coercing other values of type string
#>   a id
#> 1 1  1
#> 2 0  2
dbGetQuery(con, "SELECT 'q' AS a, 1 AS id UNION SELECT 1, 2 ORDER BY id")
#> Warning in result_fetch(res@ptr, n = n): Column `a`: mixed type, first seen
#> values of type string, coercing other values of type integer
#>   a id
#> 1 q  1
#> 2 1  2

^{Created on 2020-11-03 by the reprex package (v0.3.0)}

Use the SQL CAST(... AS ...) to coerce to a string and do more fine-grained processing in R:

library(DBI)

con <- dbConnect(RSQLite::SQLite())
dbGetQuery(
  con,
  "
  WITH tbl AS (SELECT 1 AS a, 1 AS id UNION SELECT 'q', 2 ORDER BY id)
  SELECT CAST(a AS text) AS typed_a, id 
  FROM tbl
  "
)
#>   typed_a id
#> 1       1  1
#> 2       q  2

^{Created on 2020-11-03 by the reprex package (v0.3.0)}

See Convert integer to text in SQLite's SELECT query? for more detail.

Coerce dbGetQuery to produce strings

1 Answers1