[Postgresql] String data truncated while performing conversion

[vivas89]

Hi Team,

One of our customers surfaced this issue attempting to use the DBI package to send string data into a postgresql table: https://rstudioide.zendesk.com/agent/tickets/119055

I have been able to reproduce this. Is this a bug coming from the DBI package, dbWriteTable() function? I tried using raw SQL(dbExecute) to insert the same amount of characters and it was successful. See code below for repro.

Versions:

> packageVersion("odbc")
[1] ‘1.6.1’
> packageVersion("DBI")
[1] ‘1.2.3’
> dbGetInfo(con)$driver.version
[1] "1.5.20.1026"
> dbGetInfo(con)$drivername
[1] "libpostgresqlodbc_sb64-universal.dylib"

OS: macOS Sequoia 15.5 Apple M4 Pro
RStudio: Version 2024.12.1+563.pro5 (2024.12.1+563.pro5)

Reproduction Code
This code tested character length 1019-2025. Seems to fail > 1020.

library(DBI)
library(odbc)

con <- dbConnect(drv = odbc::odbc(), dsn = "postgresql")

# Remove courses table
dbRemoveTable(con, "courses")

# Create courses table
dbExecute(con, "
CREATE TABLE IF NOT EXISTS courses (
    course_description TEXT
);
")

# Create string function that takes character length
create_string <- function(char_length) {
  paste0(rep("x", char_length), collapse = "")
}

# Test char length 1019-1025
for (char_length in 1019:1025) {
  cat("Testing", char_length, "characters: ")
  
  # Create test data
  test_data <- data.frame(
    course_description = create_string(char_length)
  )
  
  result <- tryCatch({
    dbWriteTable(con, paste0("test_", char_length), test_data, overwrite = TRUE)
    "SUCCESS"
  }, error = function(e) {
    paste0("ERROR - ", e$message)
  })
  
  cat(result, "\n")
  
  # Small delay to avoid overwhelming the connection
  Sys.sleep(0.1)
}

Result
After 1020 character insertion, it fails and gets the same error the customer was reporting when using the DBI library.

Testing 1019 characters: SUCCESS 
Testing 1020 characters: SUCCESS 
Testing 1021 characters: ERROR - nanodbc/nanodbc.cpp:1787: 00000
[RStudio][Support] (40465) String data truncated while performing conversion.  
Testing 1022 characters: ERROR - nanodbc/nanodbc.cpp:1787: 00000
[RStudio][Support] (40465) String data truncated while performing conversion.  
Testing 1023 characters: ERROR - nanodbc/nanodbc.cpp:1787: 00000
[RStudio][Support] (40465) String data truncated while performing conversion.  
Testing 1024 characters: ERROR - nanodbc/nanodbc.cpp:1787: 00000
[RStudio][Support] (40465) String data truncated while performing conversion.  
Testing 1025 characters: ERROR - nanodbc/nanodbc.cpp:1787: 00000
[RStudio][Support] (40465) String data truncated while performing conversion.  

[vivas89]

Similar to this issue when using an Oracle driver: http://github.com/r-dbi/odbc/issues/890

[detule]

Hi there:

Thanks for the report. I was able to replicate this with the PRO driver. I don't believe this is an issue with our package. My notes:

A. This issue is specific to the PRO driver. Was not able to replicate with the OEM driver.
B. Was able to mitigate with PRO driver by setting TextAsLongVarchar in the connection string ( or in the DSN definition ). For example:

  conn <- dbConnect(
    odbc::odbc(),
    dsn = "pro_postgres_db",
    uid = "<snip>",
    pwd = "<snip>", TextAsLongVarchar = 1)

C. Other flags of interest for the PRO driver:

  # MaxVarchar=255
  # MaxLongVarchar=8190

The second in particular is probably worth paying attention to, if attempting to write strings longer than 8K characters ( after setting TextAsLongVarchar).

Let me know if B. doesn't resolve the issue on your end.

Cheers

[detule]

I'll mark this as resolved for now. We can re-open to iterate if needed.