On Sat, Sep 6, 2014 at 7:28 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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 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.
