Re: psql and tab-delimited output

On 09/06/2014 12:32 AM, Abelard Hoffman wrote:
> Hi.
>
> Traditionally, to generate a TSV report, I've simply invoked psql with:
> --no-align --field-separator '\t' --pset footer=off
>
> That works in most cases, except when your column values contain tabs
> themselves.
>
> I know that COPY() will escape tabs (as \t), and we can use that from
> psql with the \copy command, but that does not include a header row of
> the column names.
>
> 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"



>
> I also considered handling the escaping myself within the SELECT, and
> then sticking with the first approach above.
>
> Suggestions?
>
> Thanks.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com