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