Why query won't save in csv file while it's seems normal in postgresql console

Question

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.

The command to export data is COPY (SELECT ... FROM ...) TO 'your_file.csv' DELIMITER ',' CSV HEADER. The rest of the options are shown here postgresql.org/docs/current/sql-copy.html — 404
– 404, Commented Dec 11, 2019 at 10:15

Jim Jones · Accepted Answer · 2019-12-11 10:34:40Z

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'"

edited Dec 11, 2019 at 10:34

answered Dec 11, 2019 at 10:24

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

10 Comments

Jim Jones Over a year ago

@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 (<query>) TO STDOUT" > /home/www/etaxi-prod-due/tmp/shara.html

Jim Jones Over a year ago

which error message are you getting? @Lotarc It might be the " you have in your strings. Have you tried escaping them?

Jim Jones Over a year ago

@Lotarc quite strange. Does the query work at all? If you use ... -c "COPY (<query>) 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.

Lotarc Over a year ago

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!

Jim Jones Over a year ago

@Lotarc Great to know it worked after all :) Cheers!

|

Collectives™ on Stack Overflow

Why query won't save in csv file while it's seems normal in postgresql console

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related