1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dot
  • 14,928
  • 41
  • 110
  • 218

1 Answers1

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'"
Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • 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 – dot Mar 15 '18 at 18:19
  • @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 – Neil McGuigan Mar 15 '18 at 18:44
  • yes, it worked perfectly! I have another kinda similar question ... here: https://stackoverflow.com/questions/49307088/deleting-records-from-a-remote-postgresql-database-using-locally-supplied-list - if you don't mind helping out. – dot Mar 15 '18 at 18:48