Formatted decimal values convert to character

[bcorwin]

It looks like DECIMAL columns in my Teradata database that are associated with a format are being pulled in to R as character instead of numeric.

con is a connection to a Teradata database:

q <- "
SELECT
  1 (BYTEINT) as \"byteint\",
  1 (SMALLINT) as \"smallint\",
  12345 (INTEGER) as \"integer\",
  123.456 as \"float\",
  1234.567890 (DECIMAL(38,6)) as \"float_formatted\",
  'abcdef' as \"string\",
  DATE '2022-06-01'  as \"date\",
  TIMESTAMP '2022-06-01 12:34:56.700000' as \"timestamp_ts\",
  TIME '12:34:56' as \"time\",
  '123' as \"integer_as_string\",
  '123.456' as \"float_as_string\";
"
rs <- dbSendQuery(con, q)
dbColumnInfo(rs)

float_formatted is correctly identified as a DECIMAL / numeric:

chk <- dbFetch(rs)
str(chk)

But is being converted to character:

What can I do to make sure that data type is converted to numeric? I know I could do that after the fact but I don't want to overcorrect and convert columns that may look to be integers / floats in R but the original database has them as characters.

[krlmlr]

Thanks. What does your dbConnect() call look like, specifically the drv argument (usually the first)? Please do not share your password or other sensitive data here.

[bcorwin]

RJDBC::dbConnect(
    RJDBC::JDBC(classPath = system.file("drivers", "Teradata", "terajdbc4.jar", package = package_name)),
    glue::glue("jdbc:teradata://{host}/DATABASE={db},DBS_PORT={port},CHARSET=UTF8,LOGMECH=ldap"),
    user,
    password
)

[bcorwin]

I ended up doing this as a fix on my end:

    res <- do.call(DBI::dbSendQuery, params)

    column_types <- DBI::dbColumnInfo(res)$data.type
    out <- DBI::dbFetch(res)
    DBI::dbClearResult(res)

    ## Make sure formatted numeric columns stay as numeric
    columns_to_adjust <- colnames(out)[
      (sapply(out, class) == "character") &
        (column_types == "numeric")]
    out <- dplyr::mutate_at(out, columns_to_adjust, as.numeric)

[krlmlr]

Thanks for the heads-up!

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.