2

I have the following logic in a bash script that works:

 #copy records to delete file to respective servers.  faster than running locally
 scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null $dir/$server.records_to_delete.txt $server:/tmp/

 # trigger the deletion on remote machine.
 deletion_status=$(psql -U test testdb -h $server -c "CREATE TEMP TABLE tmp_cdr (id int); COPY tmp_widgets FROM '/tmp/$server.records_to_delete.txt'; DELETE FROM widgets USING tmp_cdr WHERE widgets.id = tmp_widgets.id;")

But I'm trying to consolidate into one command. So instead of scp'ing the list of records to delete over to the remote server, I'd like to keep it local and just somehow create tmp_widgets using the local file.

Any suggestions would be appreciated

dot
  • 14,928
  • 41
  • 110
  • 218
  • Just to make sure I got it right: you're spliting in two commands just because of file location for `COPY`, right? – Jim Jones Mar 15 '18 at 18:50
  • yeah exactly. so I scp a list of records that need to be deleted from a remote database, over to the remote database server. And then i use the "COPY" command to create a temp table. in my first attempt, i was looping through the list (txt file) and deleting one by one via psql using -h param. But that was molasses slow. Then i discovered that I could create a tmp table using the list, which is great. But I want to leave the list locally instead of having to scp it over first. – dot Mar 15 '18 at 18:54

1 Answers1

2

You don't need to copy the file to the server. What you're looking for is the STDIN for COPY:

cat records_to_delete.txt | psql $server -c "COPY tmp_widgets FROM STDIN;"
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • hm ok. wondering i can keep my sql statement exactly the same and just tack on your suggested answer. will give it a try now. – dot Mar 15 '18 at 19:02
  • 1
    cool. that works. the only thing is you can't use "\COPY". if you update your answer to just have the COPY i can mark it as the official answer – dot Mar 15 '18 at 19:08
  • Exactly, `\copy` is the psql version of `copy`.. but in fact, you can use both :-) Glad it helped! – Jim Jones Mar 15 '18 at 19:10