Re: psql and tab-delimited output

On Sat, Sep 6, 2014 at 7:28 AM, Adrian Klaver <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 looked at the options for COPY's CSV format, but I don't see a way to
disable quoting but still have escaping.

This works, although it's not exactly simple:

DROP TABLE IF EXISTS tsv_test;

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');

SELECT * FROM tsv_test WHERE FALSE; -- to generate header row
COPY tsv_test TO STDOUT;

And then run that through psql with the --no-align --field-separator '\t'
--pset footer=off options.
With that, I'd probably generate the report into a temp table, and then run
the above to actually export that table as TSV.

@Thomas, yes, I was hoping to stick with just psql, but I'll look at other
tools if necessary.

Any other ideas?

Thanks.