odbcパッケージ経由だとvarcharが255文字に切り詰められる問題

ハマるの2回目なのでメモっておく。

PostgreSQLで“test”という名前のデータベースが存在していて、そこに接続している状態を想定する。

con <- odbc::dbConnect(odbc::odbc(), "test")

PostgreSQLのcharacter varying(varchar)は、長さ指定なしで使うと文字列の長さの制限がなくなる(cf. 8.3. 文字型)。

しかし、これをodbcパッケージ経由で取得すると255文字に切り詰められる(https://github.com/r-dbi/odbc/issues/202 で報告されているのと同じ現象?)。

library(dplyr)
query <- "SELECT (repeat('a', 1000))::varchar as str;"
odbc::dbGetQuery(con, glue::glue("CREATE TABLE x AS {query}"))
odbc::dbGetQuery(con, "SELECT str FROM x;") %>% nchar()
## str 
## 255

これは、TEXT型にキャストするととりあえず回避できる。

odbc::dbGetQuery(con, "SELECT CAST(str AS TEXT) FROM x;") %>% nchar()
##  str 
## 1000

dplyrの場合は、as.character()を使う。

tbl(con, "x") %>% mutate(str = as.character(str)) %>% pull() %>% nchar()
## [1] 1000

as.character()はTEXT型へのキャストをするSQLを生成するので、やっていることは同じ。

dbplyr::translate_sql(as.character(x))
## <SQL> CAST("x" AS TEXT)