0

I am trying to connect a local PostgrelSQL database from Rstudio via the RPostgres package.

con <- dbConnect(RPostgres::Postgres(),
dbname="####",
host="127.0.0.1",
port=5432,
user="postgre",
password="####")

it returns unreadable characters:

Error: ��������:  �û� "postgre" Password ��֤ʧ��

I learned it might be a local encoding language issue.

Sys.getlocale()
[1] "LC_COLLATE=Chinese (Simplified)_China.utf8;LC_CTYPE=Chinese (Simplified)_China.utf8;LC_MONETARY=Chinese (Simplified)_China.utf8;LC_NUMERIC=C;LC_TIME=Chinese (Simplified)_China.utf8"

So I try setting the locale as:

Sys.setlocale(category = "LC_ALL",locale = "English_United States.1252")

Then the error becomes:

Error: <d6><c2><c3><fc><b4><ed><ce><f3>:  <d3><U+00FB><a7> "postgre" Password <c8><cf><U+05A4><U+02A7><b0><dc>

How to solve this issue?

The PostgrelSQL database on my Windows PC is encoded in Chinese (Simplified)_China.936. and the Rstudio sessioninfo are:

R version 4.2.0 (2022-04-22 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)

Matrix products: default

locale:
[1] LC_COLLATE=zh-cn.UTF-8  LC_CTYPE=zh-cn.UTF-8    LC_MONETARY=zh-cn.UTF-8 LC_NUMERIC=C            LC_TIME=zh-cn.UTF-8    

attached base packages:
[1] graphics  grDevices utils     datasets  stats     methods   base     

other attached packages:
 [1] DBI_1.1.3       RPostgres_1.4.4 forcats_0.5.1   stringr_1.4.0   dplyr_1.0.9     purrr_0.3.4     readr_2.1.2    
 [8] tidyr_1.2.0     tibble_3.1.7    ggplot2_3.3.6   tidyverse_1.3.2
Phil
  • 7,287
  • 3
  • 36
  • 66
Yiwei Zhu
  • 25
  • 3
  • There's no LC_ALL on Windows. First try upgrading to the latest R version. Then try *outside* RStudio, in a console or even better Windows Terminal window. WT should just work. Windows strings are natively Unicode but before Windows 10, Windows Terminal and WSL, the console was neglected and getting UTF8 to display properly required changing console properties or running `chcp 65001` to change the console's codepage. – Panagiotis Kanavos Sep 23 '22 at 13:47
  • R itself causes problems because it assumes that strings are encoded in whatever the user's locale is. Which, on Windows, is definitely not the case. Never mind that such a thing would never work if a user in China or Poland wanted to process files in Cyrillic. RStudio and R had trouble adjusting to a natively Unicode OS and R 4.2.0 completely reworked UTF8 output. 4.2.1 fixed even more bugs – Panagiotis Kanavos Sep 23 '22 at 13:51

0 Answers0