2

I have this query which i want to save in csv file or html

select phone_number, count(driver_callsign), driver_callsign from archived_order where data like '%"ptt":3%' and completed is true and ds_id = 16 and created > (select current_date - interval '7 days') group by archived_order.phone_number, archived_order.driver_callsign HAVING COUNT(driver_callsign) > 1;

When i using it in psql console - it seems normal. There is output:

 phone_number  | count | driver_callsign
---------------+-------+-----------------
 +380502270347 |     2 | 6686
 +380502336770 |     2 | 4996

When i'm using this command:

psql -t -A -F ';' -h localhost -U username -c "select phone_number, count(driver_callsign), driver_callsign from archived_order where data like '%"ptt":3%' and completed is true and ds_id = 16 and created > (select current_date - interval '1 days') group by archived_order.phone_number, archived_order.driver_callsign HAVING COUNT(driver_callsign) > 1;" > SomeName.csv

It doesn't writing anything there.

If someone can help to fix it, i will appreciate it.

Lotarc
  • 89
  • 1
  • 7
  • 3
    The command to export data is `COPY (SELECT ... FROM ...) TO 'your_file.csv' DELIMITER ',' CSV HEADER`. The rest of the options are shown here https://www.postgresql.org/docs/current/sql-copy.html – 404 Dec 11 '19 at 10:15
  • Look comment in answer below please – Lotarc Dec 11 '19 at 10:34

1 Answers1

2

You were very close.

Try using stdout to direct the output of your query to a file using psql from your console. The following example creates a file in the client machine:

$ psql -c "COPY (your query here!) TO STDOUT DELIMITER ';'" > file.csv

If you wish to have this output file in the server you might wanna try this:

$ psql -c "COPY (your query here!) TO '/path/to/file.csv'"
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • i'm going to use this query in cron, and there's input like `usr/bin/psql -h localhost -U etaxi_prod_due etaxi_prod_due --html -c "">>/home/www/etaxi-prod-due/tmp/shara.html` – Lotarc Dec 11 '19 at 10:34
  • 1
    @Lotarc doesn't the first option of my answer work? Just place the command in your cron job: `$ sr/bin/psql -h localhost -U etaxi_prod_due etaxi_prod_due --html -c "COPY () TO STDOUT" > /home/www/etaxi-prod-due/tmp/shara.html` – Jim Jones Dec 11 '19 at 10:37
  • oh i see i'll try – Lotarc Dec 11 '19 at 11:01
  • Alright, i tried it with different combination. It's not working for me) I guess it's all because of count's and having count. But i don't know how to optimize to do this kind of stuff without it – Lotarc Dec 11 '19 at 11:23
  • 1
    which error message are you getting? @Lotarc It might be the `"` you have in your strings. Have you tried escaping them? – Jim Jones Dec 11 '19 at 11:30
  • i'm getting no erros, just an empty file – Lotarc Dec 11 '19 at 11:41
  • 1
    @Lotarc quite strange. Does the query work at all? If you use `... -c "COPY () TO STDOUT"` and the file is still empty it might be that your query isn't returning anything and the problem isn't `copy` but the query itself. – Jim Jones Dec 11 '19 at 11:45
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/204048/discussion-between-lotarc-and-jim-jones). – Lotarc Dec 11 '19 at 11:48
  • 1
    I found a solution. It was a json data which was getting wrong value, but before it's worked correctly. Thank you for your help! – Lotarc Dec 11 '19 at 13:29
  • 1
    @Lotarc Great to know it worked after all :) Cheers! – Jim Jones Dec 11 '19 at 13:41