UTF-8 / Unicode Text Encoding with RPostgreSQL
encoding, postgresql, r, rpostgresql, utf-8
Solution
After exporting to R it's shown as: "Stéphane" (the é is encoded as é)
Your R environment is using a 1-byte non-composed encoding like latin-1 or windows-1252. Witness this test in Python, demonstrating that the utf-8 bytes for `é`, decoded as if they were latin-1, produce the text you see:
>>> print u"é".encode("utf-8").decode("latin-1")
é
Either `SET client_encoding = 'windows-1252'` or fix the encoding your R environment uses. If it's running in a `cmd.exe` console you'll need to mess with the `chcp` console command; otherwise it's specific to whatever your R runtime is.
Problem
I'm running R on a Windows machine which is directly linked to a PostgreSQL database. I'm not using RODBC. My database is encoded in UTF-8 as confirmed by the following R command: ``` dbGetQuery(con, "SHOW CLIENT_ENCODING") # client_encoding # 1 UTF8 ``` However, when some text is read into R, it displays as strange text in R. For example, the following text is shown in my PostgreSQL database: "Stéphane" After exporting to R it's shown as: "Stéphane" (the é is encoded as é) When importing to R I use the `dbConnect` command to establish a connection and the `dbGetQuery` command to query data using SQL. I do not specify any text encoding anywhere when connecting to the database or when running a query. I've searched online and can't find a direct resolution to my issue. I found this link, but their issue is with RODBC, which I'm not using. This link is helpful in identifying the symbols, but I don't just want to do a find & replace in R... way too much data. I did try running the following commands below and I arrived at a warning. ``` Sys.setlocale("LC_ALL", "en_US.UTF-8") # [1] "" # Warning message: # In Sys.setlocale("LC_ALL", "en_US.UTF-8") : # OS reports request to set locale to "en_US.UTF-8" cannot be honored Sys.setenv(LANG="en_US.UTF-8") Sys.setenv(LC_CTYPE="UTF-8") ``` The warning occurs on the `Sys.setlocale("LC_ALL", "en_US.UTF-8")` command. My intuition is that this is a Windows specific issue and doesn't occur with Mac/Linux/Unix.