How to form correct CSV line with postgres SQL

Question

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.

Version is 14.1. Client is self-written java app via jdbc connection. — Alex
– Alex, Commented Nov 1, 2022 at 7:14

Adrian Klaver · Accepted Answer · 2022-10-31 23:27:18Z

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.

answered Oct 31, 2022 at 23:27

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

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

2 Comments

Alex Over a year ago

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.

Adrian Klaver Over a year ago

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?

Collectives™ on Stack Overflow

How to form correct CSV line with postgres SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related