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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 09/06/2014 10:34 AM, Abelard Hoffman wrote:
>
> On Sat, Sep 6, 2014 at 7:28 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)__com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(at)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(dot)klaver(at)aklaver(dot)com