0

Is it possible to query Postgresql in order to get correct CSV line? For instance select concat (a,',',b) from t but with correctly escaped commas and quotes.

Burhan Ali
  • 2,258
  • 1
  • 28
  • 38
Alex
  • 51
  • 2

1 Answers1

0

A couple of options.

Using psql


select * from some_table \g (format=csv) output.csv

This will create a CSV file named output.csv.

\copy cell_per to 'output.csv' WITH(format csv, header, delimiter '|');

The above allows you to use the options as explained here COPY to do things like change the delimiter, quoting, etc.

You can also use COPY directly as a query. Though in that case it is important to note that COPY runs as the server user and can only write files to directories the server user has permissions on. The work around is to make the output go to STDOUT and and capture it. For instance using the Python driver psycopg2 there are copy methods copy.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • Sorry it was worth to note that psql wasn't an option. Client is self written. I need an sql statement probably with postgres specific function to get properly formed CSV. – Alex Nov 01 '22 at 07:22
  • There is a Postgres specific statement to output CSV it is `COPY` as mentioned in the answer. If you are using the Postgres community JDBC driver then maybe this [JDBC Copy](https://stackoverflow.com/questions/46988855/correct-way-to-use-copy-postgres-jdbc)? – Adrian Klaver Nov 01 '22 at 14:55