Insertion of datasets with integers fails because sqlData() coerces some of them to character #216
Description
I'm using RPostgres to connect with a CockroachDB database (which supports PostgreSQL syntax and drivers).
I'm able to connect to the database, create some tables and do queries and insertions, however sometimes I get an error when I try to insert data frames with numeric columns, but only if the column has integer numbers of different length in digits (e.g. 8 and 100, 300, and 5000, etc), like hp in mtcars, and price in ggplot2::diamonds.
library(RPostgres) con <- dbConnect(RPostgres::Postgres(), ...) dbWriteTable(con, "mtcars", mtcars) #> Error in connection_copy_data(conn@ptr, sql, value): COPY returned error: ERROR: could not parse " 93" as type float: strconv.ParseFloat: parsing " 93": invalid syntaxSame thing happens with ggplot2::diamonds. However, if I exclude the column hp in mtcars (or price in diamonds), the operation is successfully completed.
library(RPostgres) con <- dbConnect(RPostgres::Postgres(), ...) dbWriteTable(con, "mtcars", dplyr::select(mtcars, -hp))I have digged down to the code of the method dbWriteTable() and I suspect that the problem may be in sqlData(), which is used inside dbWriteTable() after the table has been created with the appropriate data types (i.e. specifying those columns as integers).
This is what I get when I apply sqlData() to mtcars. Please note the data type and the third element in the column hp.
str(sqlData(con, mtcars)) #> 'data.frame': 32 obs. of 11 variables: #> $ mpg : num 21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ... #> $ cyl : chr "6" "6" "4" "6" ... #> $ disp: num 160 160 108 258 360 ... #> $ hp : chr "110" "110" " 93" "110" ... #> $ drat: num 3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ... #> $ wt : num 2.62 2.88 2.32 3.21 3.44 ... #> $ qsec: num 16.5 17 18.6 19.4 17 ... #> $ vs : chr "0" "0" "1" "1" ... #> $ am : chr "1" "1" "1" "0" ... #> $ gear: chr "4" "4" "4" "3" ... #> $ carb: chr "4" "4" "1" "1" ...However, this doesn't happen when I provide another connection type as first argument to sqlData() (for example, an SQL Server connection through odbc).
library(odbc) odbc_con <- dbConnect( odbc::odbc(), driver = "ODBC Driver 11 for SQL Server", ... ) str(sqlData(odbc_con, mtcars)) #> 'data.frame': 32 obs. of 12 variables: #> $ row_names: chr "Mazda RX4" "Mazda RX4 Wag" "Datsun 710" "Hornet 4 Drive" ... #> $ mpg : num 21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ... #> $ cyl : num 6 6 4 6 8 6 8 4 4 6 ... #> $ disp : num 160 160 108 258 360 ... #> $ hp : num 110 110 93 110 175 105 245 62 95 123 ... #> $ drat : num 3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ... #> $ wt : num 2.62 2.88 2.32 3.21 3.44 ... #> $ qsec : num 16.5 17 18.6 19.4 17 ... #> $ vs : num 0 0 1 1 0 1 0 1 1 1 ... #> $ am : num 1 1 1 0 0 0 0 0 0 0 ... #> $ gear : num 4 4 4 3 3 3 3 4 4 4 ... #> $ carb : num 4 4 1 1 2 1 4 2 2 4 ...sessioninfo::session_info() #> - Session info ---------------------------------------------------------- #> setting value #> version R version 3.4.1 (2017-06-30) #> os Windows 7 x64 SP 1 #> system x86_64, mingw32 #> ui RTerm #> language (EN) #> collate Spanish_Chile.1252 #> ctype Spanish_Chile.1252 #> tz America/Santiago #> date 2019-04-10 #> #> - Packages -------------------------------------------------------------- #> package * version date lib source #> assertthat 0.2.0 2017-04-11 [1] CRAN (R 3.4.4) #> cli 1.0.0 2017-11-05 [1] CRAN (R 3.4.4) #> crayon 1.3.4 2017-09-16 [1] CRAN (R 3.4.4) #> digest 0.6.15 2018-01-28 [1] CRAN (R 3.4.3) #> evaluate 0.10.1 2017-06-24 [1] CRAN (R 3.4.4) #> htmltools 0.3.6 2017-04-28 [1] CRAN (R 3.4.4) #> knitr 1.20 2018-02-20 [1] CRAN (R 3.4.4) #> magrittr 1.5 2014-11-22 [1] CRAN (R 3.4.4) #> Rcpp 1.0.0 2018-11-07 [1] CRAN (R 3.4.4) #> rmarkdown 1.11 2018-12-08 [1] CRAN (R 3.4.4) #> sessioninfo 1.1.1 2018-11-05 [1] CRAN (R 3.5.3) #> stringi 1.1.7 2018-03-12 [1] CRAN (R 3.4.4) #> stringr 1.3.1 2018-05-10 [1] CRAN (R 3.4.4) #> withr 2.1.2 2018-03-15 [1] CRAN (R 3.4.4) #> yaml 2.1.19 2018-05-01 [1] CRAN (R 3.4.4) #> #> [1] C:/R-3.4.1/library