2

I have been trying to execute PSQL from system() within R in RStudio. I have PSQL setup in my PATH and can execute PSQL from the cmd line. I cannot for the life of me figure out the correct method for executing psql from within R on windows. I have code supplied from a ubuntu environment. I have not used system() previously before this and researching for this specific issue has been unsuccessful.

The hardest part is not receiving any output after executing system in R. I have tried a few different setting from looking at ?system. With no luck.

This code should execute a simple sql statement and pass the output to a local file. Ultimately this will be more robust to include dynamic elements in an application. Just having the basics working seems like the hardest part.

system(paste("export PGPASSWORD=db_password;psql -h db_host -d db_name -c 'copy(select * from large_table limit 1000) to stdout csv' > C:/temp_data/db_test.dat", sep=""))

I am curious as to if anyone has a working windows environment using PSQL in R. My greenplum server is not local.

My echo %PATH% includes C:\Program Files (x86)\pgAdmin III\1.12 included in both system and user vars.

Hong Ooi
  • 56,353
  • 13
  • 134
  • 187
dotcomken
  • 247
  • 2
  • 8
  • I simplified the string in paste for my question so it may look strange using paste with one string. – dotcomken Feb 24 '14 at 02:01
  • 1
    `export` isn't a command on Windows. You probably want `set`. – Hong Ooi Feb 24 '14 at 02:29
  • 1
    You can also set the password [for `postgres` or other user] using [the pgpass file](http://www.postgresql.org/docs/current/static/libpq-pgpass.html). This can be [set up with pgAdmin](http://www.pgadmin.org/docs/1.4/pg/libpq-pgpass.html). [More here](http://stackoverflow.com/questions/6523019/postgresql-scripting-psql-execution-with-password). – Mike T Feb 24 '14 at 02:41
  • Note that your pgAdmin is ancient. – nograpes Feb 24 '14 at 05:50
  • @nograpes Wouldn't think the pgAdmin version matters much when the db version is 8.2.15. Would the most recent be more efficient for older versions? – dotcomken Feb 25 '14 at 16:25
  • It is actually your `psql` version that matters, but I assume that pgAdmin comes with a version of `psql`? Your db is also old, so I guess it is okay to use an old `psql`. I'm not sure if there will be any efficiency changes if you move to a new `psql`. There might be bug fixes though. – nograpes Feb 25 '14 at 18:05

1 Answers1

2

There are a few problems with your command.

  • system cannot be used with redirects, you must use shell
  • You cannot use single quotes to quote commands in Windows, you must use double quotes.
  • To concatenate commands, you use the & operator, not a ; like in Unix.

So your command would look like (it appears to be necessary to include this in one line):

cmd<-'set PGPASSWORD=db_password& psql -h db_host -d db_name -c "copy(select * from large_table limit 1000) TO STDOUT CSV;" > C:/temp_data/db_test.dat'
shell(cmd)

But, have you considered using the RPostgresql driver, which is a much simpler, platform-independent way to do your task?

# Load up the driver
library(RPGsql)
drv <- dbDriver("PostgreSQL")
# Create a connection
con <- dbConnect(drv, dbname="db_name", host='db_host',password='db_password',user='db_user')
# Query the database
db_test=dbGetQuery(con, 'select * from large_table limit 1000')
# Write your file
write.csv(db_test,'C:/temp_data/db_test.dat')
nograpes
  • 18,623
  • 1
  • 44
  • 67
  • Yes I considered RPostgresql and have used PivotalR since greenplum is build on top of pivotal. The reason I'm trying this method is for pushing data into tables. There are certain limitations for security reasons. I have a 30min window for this process. I heard that psql is the preferred way to do this. Just need a good test case to benchmark. – dotcomken Feb 25 '14 at 16:43
  • Hm, it sounds like you have a good reason for doing it this way. Did you try the command `cmd` that I provided? Did it work the way you expected? – nograpes Feb 25 '14 at 18:02
  • If you want to write data to the database, RPostgreSQL provides a `dbWriteTable` function for that as well. – Hong Ooi Feb 27 '14 at 04:12
  • The shell execution works perfectly up until: > C:/temp_data/db_test.dat This line doesnt seem to have any effect or execute. The results are posted to the console. Does the output of a shell require special method to save to file or the R environment in general? – dotcomken Mar 11 '14 at 17:06
  • @dotcomken The redirection works on my machine. I think I know what is happening: put the `cmd<-...` on one line, it appears that R is parsing the `\n`s and passing them along to `shell`, which doesn't work. – nograpes Mar 11 '14 at 17:32
  • Adding all to a single line worked perfectly. Thanks again for your help. – dotcomken Mar 11 '14 at 22:38