How to run a SQL query on a remote machine and save locally in CSV format

Question

I have the following snippet of code in a bash script:

psql -U ppd ppd -h $remote_server -t -c "copy (SELECT CASE WHEN DATE_TRUNC('MONTH', ABSTIME(start_ts)) = DATE_TRUNC('MONTH', CURRENT_DATE) THEN 'current' ELSE DATE_TRUNC('MONTH', ABSTIME(start_ts))::DATE::TEXT END, 
    id, 
    status, 
    class, 
    dir, 
    volid, 
    f_user, 
    t_user, 
    ABSTIME(start_ts),
    ABSTIME(end_ts), 
    elapsed, 
    callid, f_uri, t_uri, lcr_tag 
    FROM ppd_widgets) to '/tmp/"$server".db' With CSV DELIMITER ',';"

scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null "root@$server:/tmp/$server.db" "$dir/"

I'd like to know if there's a way to do it in one shot - aka instead of creating a csv file on the remote server and then copy it over, can I select the records and display in csv format?

Then I could do something like this:

 psql -U ppd ppd -h $remote_server -t -c "select that returns in csv style" >> save_to_local.csv

Any tips / suggestions on how I can do this?

Neil McGuigan · Accepted Answer · 2018-03-15 18:11:37Z

1

Use psql's \copy function (not PostgreSQL's copy function)

This lets you load or save files locally

Example:

psql -c "\copy (select ...) to 'local-file.csv'"

answered Mar 15, 2018 at 18:11

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

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

3 Comments

dot Over a year ago

so that will run the query against the remote server defined by "-h" but save the file locally on the machine where the psql command is triggered? Will give it a go right now

Neil McGuigan Over a year ago

@dot yes if you set the remote host name it will run that query against that server, but save the file locally. \copy also has the advantage of not requiring superuser privileges

dot Over a year ago

yes, it worked perfectly! I have another kinda similar question ... here: stackoverflow.com/questions/49307088/… - if you don't mind helping out.

Collectives™ on Stack Overflow

How to run a SQL query on a remote machine and save locally in CSV format

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related