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

[Edward Vanden Berghe]

Hi James,

Thanks for your quick response.

The format of the options of the copy command was changed some time ago; your suggestion does not work; the one in my original post does.

In the mean time I found the source of the problem - nothing to do with DBI or PostgreSQL, but a mistake in my code.

Cheers,

Edward

From: James David Smith [mailto:james.david.smith using gmail.com] 
Sent: 01 December 2013 15:37
To: Edward Vanden Berghe
Cc: r-sig-db using r-project.org
Subject: Re: [R-sig-DB] unexplained error in response to 'copy' command

Does this work?
dbSendQuery(con, "copy testtable to '/home/evberghe/testdirectory/testfile.csv' format csv delimiter ',' header=true  quote '\"')



On 1 Dec 2013 14:20, "Edward Vanden Berghe" <evberghe using gmail.com> wrote:
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]]

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB using r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-db