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.