For today, I have another PostgreSQL tip, which I admit is extremely niche, but useful nevertheless.
As I mentioned a few times, I’m a big fan of psql. One of the nice things it has to offer is the \copy command. It is a very thin wrapper around SQL’s copy command, with an important change: it generates files on the local filesystem, and they are owned by the user who runs psql, not the postgres user (under whom the PostgreSQL server operates).
However, \copy does not accept multiline queries, which is a pain if you have a long, nicely formatted query and you just want to feed it to \copy to get a result e.g. as a csv file.
Today I had a nice idea (in fact, I was not the first one to have it) about how to overcome this limitation. You can simply use the query in a create temp view statement, creating a temporary view, and then say \copy (table <view_name>) to <file_name> to get your csv file (or whatever output you need). This trick is especially nice because psql uses the GNU Readline library, so you can just press the “up” arrow after issuing your query and then add create temp view
<view_name> as before your long query.
Happy copying!