On Sun, Sep 7, 2014 at 12:28 PM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote:
On Sat, Sep 6, 2014 at 12:32 AM, Abelard Hoffman <abelardhoffman@xxxxxxxxx> 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: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.klaver@xxxxxxxxxxx> 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.
