On Sat, Sep 6, 2014 at 12:32 AM, Abelard Hoffman <abelardhoff...@gmail.com> wrote:
> Hi. > > Traditionally, to generate a TSV report, I've simply invoked psql with: > --no-align --field-separator '\t' --pset footer=off > > That works in most cases, except when your column values contain tabs > themselves. > > I know that COPY() will escape tabs (as \t), and we can use that from psql > with the \copy command, but that does not include a header row of the > column names. > Which is a shame. \copy really should allow HEADER in the default format, not just CSV format. And it on the to-do list, just hasn't be done yet: https://wiki.postgresql.org/wiki/Todo#COPY It seems like it should be fairly easy to do. > > So, my question is, what's the simplest way to generate tab-escaped > TSV-formatted reports with the first line containing the list of column > names? > I just assume that none of the column names need escaping, and so select and join them on tabs. At one point I had a perl script that would do this for me, e.g. given a query, it would execute it once with a 'and 1=0' at the end (obviously can't be done legally/efficiently/safely with all queries) to get the column names, then again in a \COPY to get the data, but I seem to have misplaced it. It worked well as long as you understood it was a dirty hack and so had the limitations of one. Cheers, Jeff
