Home:ALL Converter>Wrong text encoding returned when query from PostgreSQL database using RPostgreSQL

Wrong text encoding returned when query from PostgreSQL database using RPostgreSQL

Ask Time:2017-06-15T12:55:52         Author:Tue Nguyen

Json Formatter

I used R and DBI to query data from a PostgreSQL but got corrupted results with weird characters. I check both server encoding and client encoding, and they are all UTF-8. Here's what I did

  1. Connect to the db


    conn <- dbConnect( drv = RPostgreSQL::PostgreSQL(), host = *, dbname = , user = , password = * )

  2. Fetch the result

    result_df <- dbGetQuery( conn, "select description from some_table where user_id = 1234" )

  3. Result What I got is ã¯ã˜ã‚ã¾ã—ã¦1008(ã¨ãƒ¼ã‚„orã¨ã…ーや)ã¨ã„ã„ã¾ã™ã€‚ 表ç¾ã™ã‚‹ã®ãŒå¤§å¥½ããªå­ã§ã™!! 感性ãŒäººã¨ã‚ºãƒ¬ã¦ã¾ã™(笑) ãŠæ­Œã‚’ã‚‚ã£ã¨ã†ã¾ããªã‚ŠãŸã„ã§ã™ã€‚ while the actual text is はじめまして1008(とーやorとぅーや)といいます。 表現するのが大好きな子です!! 感性が人とズレてます(笑) お歌をもっとうまくなりたいです。

I test again by reading the text and then print it back

x <- 'はじめまして1008(とーやorとぅーや)といいます。 表現するのが大好きな子です!! 感性が人とズレてます(笑) お歌をもっとうまくなりたいです。'
>> はじめまして1008(とーやorとぅーや)といいます。 表現するのが大好きな子です!! 感性が人とズレてます(笑) お歌をもっとうまくなりたいです。

There's nothing wrong with the encoding in this case, so I think the problem happened when the data is returned from the database into R, but I don't know how to fix it.

Author:Tue Nguyen,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/44558854/wrong-text-encoding-returned-when-query-from-postgresql-database-using-rpostgres