I am connecting to a PostgreSQL database and I would like to make use of psql commands (especially the \copy command) from within R.
I’m on a windows client using ODBC drivers to connect to the database. Basically any of the major ODBC package in R, including the „rpg“ package, is working to connect to the database and to read and write tables etc.
Apart from placing regular SQL queries the „rpg“ package allows to make use of psql commands. The „copy_to“ function should send the psql „\copy“ command to the database.
However, when running the function I get the error: „psql not found“
I also have pgAdmin III installed. Here running the \copy command is no problem at all.
Digging deeper I found that the rpg::copy_to function first runs Sys.which(„psql“) which returns: "" leading to said error.
Reading this thread made me think that adding the path to the pgAdmin psql.exe would do the trick. So I added the line
psql=C:\Program Files (x86)\pgAdmin III\1.16\psql.exe
in the R environment.
Running Sys.which(„psql“) still returns "", while Sys.getenv() correctly shows the path to the pqsl.exe that I specified.
How can I make Sys.which() find the psql.exe? Given that’s the correct way to solve this issue in the first place.
I would appreciate any help!
