odbc Postgres Driver Settings to Avoid Truncating JSON(b)

Last year I wrote about how the odbc R package truncates JSON at 255 characters. There was also an issue opened by Etienne B. Racine about how binary objects truncated to 255 characters when read which is a better generalization of the problem.

My previous solution was to cast the field as text prior to collecting it from the database, e.g. mutate(field = as.character(field)). Of course this just hides the real problem (which lies somewhere in the nanodbc driver).

However I do have a new solution that is better and very straightforward – simply specify the MaxVarCharSize driver option. This driver option controls the maximum length of character fields to return. By setting the maximum varchar size to zero, the driver will return the full-length of the data.

For example:

conn <- dbConnect(odbc::odbc(), "postgres", timeout = 10, maxvarcharsize=0)

Instead of specifying the driver option in your R code, you can set it in your odbcinst.ini file (in /etc/odbcinst.ini on Linux or $HOME/.odbcinst.ini on macOS).

For example:

[PostgreSQL Unicode]
Driver           = /usr/local/lib/psqlodbcw.so
BoolsAsChar    = No
MaxVarcharSize = 0

I prefer to set my driver configuration in the odbcinst.ini file so that I don’t forget to set it in the dbConnect statement. The tradeoff is that the configuration is then undocumented in your code and someone else might encounter an error due to different driver settings.