On Sat, Sep 6, 2014 at 12:32 AM, Abelard Hoffman <abelardhoffman@xxxxxxxxx> wrote:
Hi.Traditionally, to generate a TSV report, I've simply invoked psql with:--no-align --field-separator '\t' --pset footer=offThat 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:
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