History of 2022-02-28 A clever trick with psql copy and multiline select statements

2022-02-28
18:56 UTC Revision 1 . . . . mborkFor 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.