0

I'm trying to get some data from a Postgres DB via the RPostgreSQL package. The character columns in the resulting data.frame have an unknown encoding:

Encoding(raw_orders$city)
  [1] "unknown" "unknown" "unknown" "unknown" "unknown" "unknown" "unknown" "unknown" "unknown" "unknown" "unknown" "unknown"

This despite me setting every imaginable encoding flag to UTF-8:

> dbGetQuery(connection_postgres, "SHOW CLIENT_ENCODING")
  client_encoding
1            UTF8

> dbGetQuery(connection_postgres, "SHOW SERVER_ENCODING")
  server_encoding
1            UTF8

> Sys.getlocale()
[1] "en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8"

How do I force UTF-8 for all character columns without having to manually set it via Encoding()?

RoyalTS
  • 9,545
  • 12
  • 60
  • 101
  • Hi RoyalTs Could you give us some sample data? and the output from psql -l / SHOW SERVER_ENCODING; on your server? and which command did you use to pull the data into R so I can try to replicate. –  Jun 07 '16 at 19:52
  • @pnorton See my edit above for the results of `SHOW SERVER_ENCODING`. I pull the data into R using `raw_orders <- dbGetQuery(connection_postgres, query)` where `query` is a fairly large SQL statement involving several `LEFT JOIN`s and `connection_postgres` is a `dbConnect()` – RoyalTS Jun 07 '16 at 21:21
  • Hi RoyalTS. I'm all out. What results (from query) do you get from a postgresql client such as psql or pgadmin ? All the best apart from that –  Jun 08 '16 at 08:38

0 Answers0