I have a JDBI query that returns many columns from few tables which I need to write to a CSV file. How can I achieve this? I have a REST end point that receives POST of search criteria using which I am constructing a query to fire against the Postgres db. I am using Dropwizard and JDBI.
Add a comment |
1 Answer
Use COPY TO.
COPY (SELECT ... ) TO '/path/to/myfile1.csv' FORMAT csv; You need superuser privileges if you want to write to a file. The manual:
COPYnaming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.
Or you can send the output to STDOUT directly without saving to a file (possible without superuser privileges!).
To write the file on your client (can be separate from the DB server or not) use the meta-command \copy of psql encapsulating the same functionality. This does not require superuser privileges:
This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.
Details:
answered Apr 14, 2016 at 22:18
Erwin Brandstetter
668k159 gold badges1.2k silver badges1.3k bronze badges
4 Comments
pandaadb
Alternatively, you can use a CSVWriter (OpenCsv) to write this. Doing that, next time you need to switch out your database solution, your application won't break (hopefully)
Nikki
I can't use the
COPY due to superuser privilege requirements. We are using SuperCSV which has CsvResultSetWriter. But, not sure how to achieve this as my actual query result is set of columns from few tables and I don't have to have @RegisterMapper class and I don't have one.
Erwin Brandstetter
@Nikki:
\copy of psql does not require superuser privileges. Consider added details.pandaadb
@Nikki I would make the query return an object. I feel like an application call writing a file to disk straight from the DB is a weird approach. Then execute my call and use a normal CSVWriter to write the file