Re: psql and tab-delimited output

On Sun, Sep 7, 2014 at 12:28 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Sat, Sep 6, 2014 at 12:32 AM, Abelard Hoffman <abelardhoffman(at)gmail(dot)com
> > wrote:
>
[snip]

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

Ah, excellent. That would solve most of my issue.

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

Yes, that's exactly what I need, although I'm loathe to run the queries
twice just to get the headers.

Thanks to everyone for their comments and suggestions.

As additional background, I have a dozen or so "reports" that exist as
plain text files, and they're just run by cron with psql and mailed to
those who are interested in them. Historically, all of them have been TSV.
It's worked fine since all of the data has been computed (e.g., sales
counts, etc.). But with a recent report, we're including a customer
feedback comment, which obviously can have tabs and newlines, etc.

On Sun, Sep 7, 2014 at 9:25 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
>
> I also took a look at the psql source. It doesn't look like it would be
>> very hard to add some backslash escaping logic as an option. Am I the
>> only one that would use that? For reports, everyone else mostly uses
>> other tools? I'd like to stay away from GUI-tools, if possible.
>>
>
> Not sure what you are asking for here. Something like this?:
> http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html

I was meaning, it seems like it would be useful to have psql be able to
behave like COPY but without having to call \copy.
So IOW, something like:

psql --no-align --field-separator '\t' --field-escape

Where the --field-escape option would cause the data to be escaped exactly
like COPY does. Having the HEADER option to COPY's default format will
largely solve this though. Then it's just a matter of slurping in the
report files, making sure all newlines are removed, and passing them to the
appropriate \copy command.