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

[James David Smith]

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
>

	[[alternative HTML version deleted]]