Re: psql and tab-delimited output

On 09/07/2014 01:45 AM, Abelard Hoffman wrote:
>
>
>
> On Sat, Sep 6, 2014 at 11:43 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 09/06/2014 10:34 AM, Abelard Hoffman wrote:
>
>         On Sat, Sep 6, 2014 at 7:28 AM, Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.__com
>         <mailto:adrian.klaver@aklaver.com>>> wrote:
>
>              On 09/06/2014 12:32 AM, Abelard Hoffman wrote:
>
>         [snip]
>
>                  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?
>
>
>
>              create table tsv_test (id int, fld_1 varchar);
>
>              insert into tsv_test values (1, 'test    value');
>              insert into tsv_test values (2, 'test    value');
>              insert into tsv_test values (3, 'test    value');
>
>              \copy tsv_test to 'data.tsv'  with  csv header delimiter '
>               ';
>
>              aklaver@panda:~> cat data.tsv
>              id      fld_1
>              1       "test   value"
>              2       "test   value"
>              3       "test   value"
>
>
>         Thanks, Adrian. That works, but since we're using quotes to
>         embed the
>         delimiter, we lose the simplicity of TSV. I can't just do a split on
>         /\t/ to get the fields and then unescape the values. At that
>         point it's
>         probably simpler to just switch to standard CSV.
>
>
>         Using your example, the output I'd prefer is:
>
>         id    fld_1
>         1     test\tvalue
>         2     test\tvalue
>         3     test\tvalue
>
>
>     I guess it depends on what you are using.
>
>     In Python:
>
> [snip]
>
> Yeah, I can parse CSV easily enough. My boss wants TSV though (I could
> parse CSV and split out TSV, of course). Even then, having to take the
> report query (which can be big), strip all the newlines and insert it
> into a \copy command is kind of a PITA.

I see CSV as a generic term that covers all separated value formats, so
TSV is just a variation. I am not sure exactly what the end point of all
this, so I am probably not going to be able to offer much more. One
heads up, in 9.3+ you have the option in \copy(COPY) to use an external
program to copy TO or FROM

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

4.1.2.2. String Constants with C-style Escapes

test=> select E'a\tb';
  ?column?
-----------
  a       b
(1 row)


>
> Thanks.


--
Adrian Klaver
adrian.klaver@aklaver.com