Search Postgresql Archives

Re: psql and tab-delimited output

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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=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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux