1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Nikki
  • 97
  • 10

1 Answers1

2

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:

COPY naming 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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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) – pandaadb Apr 15 '16 at 09:45
  • 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. – Nikki Apr 15 '16 at 12:31
  • @Nikki: `\copy` of psql does not require superuser privileges. Consider added details. – Erwin Brandstetter Apr 15 '16 at 13:44
  • 1
    @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 – pandaadb Apr 15 '16 at 15:21