Search code examples
postgresqlcsvjdbcdropwizardjdbi

Converting query result into CSV

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.

Solution

  • 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: