R - Postgresql Export Data to CSV

Question

I'm trying to write a script in R to export a query from a Postgresql database to a csv file.

If I do this from cmd it works fine :

psql -U postgres
\copy (select * from clickthru.train limit 10) to 'c:\\me\\psql_test.csv' with csv header;

However when I try this in R it looks like it executes (no errors) but no file is generated:

system('psql -U postgres COPY (select * from clickthru.train limit 10;) TO "C:\\me\\psql_test.csv" with CSV')

Any suggestions?

queryResult <- however you're running your query then use write.csv(queryResult, 'c:\\me\\psql_test.csv' — Feargal Ryan
– Feargal Ryan, Commented Feb 1, 2015 at 20:12
@FeargalRyan: That could work but the result set is too big to pass it to R via ODBC. Ignore the 'limit 10' in the example. — screechOwl
– screechOwl, Commented Feb 1, 2015 at 20:29
Hey, looks like this behaviour has been encountered before here: stackoverflow.com/questions/7393579/… . I think you need to concatenate your system commands with & — Feargal Ryan
– Feargal Ryan, Commented Feb 1, 2015 at 21:45

Robert · Accepted Answer · 2015-06-28 19:24:40Z

1

use;

\copy (select * from clickthru.train limit 10) to 'c:\\me\\psql_test.csv' DELIMITER ',' CSV HEADER;

Robert

5,31843 gold badges70 silver badges115 bronze badges

answered Jun 28, 2015 at 19:21

FND

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

danbst · Accepted Answer · 2018-12-05 12:18:48Z

0

There are several issues here

Query should be passed via -c argument: psql -U postgres -c '... query ...'
COPY and \copy work differently. COPY operates on servers disk space, \copy operates on client disk space. If you run both on one computer, then the difference is that COPY will work as postgres user, and \copy as user who runs the script.
In your example you have extra semicolon (;) in query ...COPY (select * from clickthru.train limit 10;)...

answered Dec 5, 2018 at 12:18

danbst

3,70322 silver badges40 bronze badges