Today I Learned

A Hashrocket project

Ready to join Hashrocket? Find Openings here and apply today.

Export CSV of query without COPY

Clients task me to export data to a CSV from time to time. My favorite workflow for authoring queries is a split tmux session, vim (with the excellent coc-sql) on one side, and a psql session on the other. I modify my query and then re-execute via \i.

This is great, until I’m ready to export the CSV. historically I’ve edited the query, cumbersomely breaking my REPL flow by turning it into a COPY command. Now I do this:

\pset format csv
\o output-file.csv
\i my-query.sql

To reset my psql session output…

\pset format aligned

And thus my-query.sql remains a working query.

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.