Psql output csv file formatting

Question

Trying to make batch file that will get query from script file and put results into csv. Batch file code:

psql -h host -d test -U test -p 5432 -a -q -f C:\Users\test\Documents\my_query.sql TO STDOUT WITH CSV HEADER DELIMITER ';' > C:\Users\test\Documents\res.csv

In result file I'm getting result like this:

select *

from public.test

limit 3

    id    |    name     | count_01
----------+------------+---------------+
 11021555 | a       |             1 |
 39534568 | b       |             2 |
 11695210 | c       |             3 |

(3 rows)

How to get only script results without rows count and symbols like '|' or '+' and using ';' delimetres as in the usual csv file?

Working script:

psql -h host -d test -U test -p 5432 -q --quiet --no-align --field-separator=';' --file=C:\Users\test\Documents\my_query.sql --output=C:\Users\test\Documents\res.csv

Have you try "`--no-align`" and "`--field-separator=`" options? — Daemon-5, Oct 05 '21 at 15:15
Pretty sure you are getting the output of whatever is `my_query.sql` and never getting to the `COPY`. Add the contents of `my_query.sql` to your question. — Adrian Klaver, Oct 05 '21 at 15:34
Have you used the site search facility at the top of the page, when seeking examples of the use of the [available options](https://www.postgresql.org/docs/current/app-psql.html), you should have read? The first hit I found was [this](https://stackoverflow.com/q/1120109). — Compo, Oct 05 '21 at 15:35
Using `--field-separator=,` will only work reliably if the data contain no commas... — Laurenz Albe, Oct 06 '21 at 07:18

Laurenz Albe · Answer 1 · 2021-10-06T07:14:51.593

1

From PostgreSQL v12 on, you can use the CSV output format of psql:

psql --quiet --csv --file=my_query.sql --output=res.csv

--quiet suppresses the psql welcome message.

edited Oct 06 '21 at 07:14

answered Oct 05 '21 at 16:05

Laurenz Albe

209,280
17
206
263

1

When trying to add '--csv' getting an error 'psql: illegal option -- csv' – FeoJun Oct 06 '21 at 06:54
Then you are using a version older than v12 of `psql`. – Laurenz Albe Oct 06 '21 at 07:15

score 0 · Answer 2 · answered Oct 05 '21 at 15:33

0

Should work with

psql -h host -d dbname -U user -p port -a -q -f my_query.sql -o res.csv --record-separator=',' --csv

answered Oct 05 '21 at 15:33

Ftisiot

1,808
1
7
13

Psql output csv file formatting

2 Answers2