Home / server / Questions / 862201
In Process
BioRod
Asked: 2017-07-15 06:05:51 +0800 CST

getting a csv file from a remote select query

  • 772

I have a client windows host that would like to connect to a Postgres database, issue a select query and return that query to the windows host a CSV file.

I do know that Postgres has a copy command that will work.

copy (SELECT * from foo) To '/tmp/forDavid.csv' with CSV DELIMITER ',';

And this customer will be accessing this SELECT query daily. The client windows host and the Postgres database is on an internal network.

I want to avoid using PHPMyAdmin because he is not savvy, I want this to be as simple as possible. I do know Perl but that would involve installing Perl on client windows host which I don't want to do.

What would be the easiest solution for this task?

windows

1 Answers

  1. What you'll want to do here is use the COPY ... TO STDOUT form of COPY and then capture the output and then capture the output on your local system or app and write that to your local file. A simple example using psql from the command line (not using psql's \copy stuff and no need to set the delimiter to a comma since that's the default for CSV output):

    psql -U ejones -h remote_host.com -c "COPY (SELECT * FROM foo) TO STDOUT CSV;" somedb > /tmp/forDavid.csv

    https://www.postgresql.org/docs/9.5/static/sql-copy.html

    • 0