[R-sig-DB] unexplained error in response to 'copy' command

[Edward Vanden Berghe]

Dear all,

 

I wanted to write the contents of a PostgreSQL table to a csv file; this is the code I used:

 

sql <- paste("set search_path to testschema; ",

                                "copy testtable to '/home/evberghe/testdirectory/testfile.csv' ",

                                "with (format csv, delimiter ',', header true, quote '\"');",

                                sep="")

dbSendQuery(con, s)

 

In R I get the response:

 

Error in postgresqlExecStatement(conn, statement, ...) : 

  RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at or near "testtable"

LINE 1: testtable

        ^

)

 

The exact same sql statement runs fine if I run it from pgAdmin. Other SQL commands work fine, so no problem with the connection. I set the permissions to the directory for anyone to read and write from/to the destination directory. Any idea of what might cause the problem?

 

I am using R version 3.0.2 though StatET (but a plain vanilla R terminal gives the same result). SessionInfo() is 

 

R version 3.0.2 (2013-09-25)

Platform: x86_64-pc-linux-gnu (64-bit)

 

locale:

[1] LC_CTYPE=en_US.UTF-8          LC_NUMERIC=C                  LC_TIME=en_US.UTF-8          

 [4] LC_COLLATE=en_US.UTF-8        LC_MONETARY=en_US.UTF-8       LC_MESSAGES=en_US.UTF-8      

 [7] LC_PAPER=en_US.UTF-8          LC_NAME=en_US.UTF-8           LC_ADDRESS=en_US.UTF-8       

[10] LC_TELEPHONE=en_US.UTF-8      LC_MEASUREMENT=en_US.UTF-8    LC_IDENTIFICATION=en_US.UTF-8

 

attached base packages:

[1] stats     graphics  grDevices utils     datasets  methods   base     

 

other attached packages:

[1] RPostgreSQL_0.4 DBI_0.2-7       rj_1.1.3-1     

 

loaded via a namespace (and not attached):

[1] rj.gd_1.1.3-1 tools_3.0.2  

 

PostgreSQL is version 9.1.10; Ubuntu is 12.04.3

 

 

Edward


	[[alternative HTML version deleted]]