Re: psql and tab-delimited output

On Sat, Sep 6, 2014 at 12:32 AM, Abelard Hoffman <abelardhoffman(at)gmail(dot)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