Re: psql and tab-delimited output

Abelard Hoffman wrote on 06.09.2014 09:32:
> 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?

Do you have to do that using psql or can you use a different tool?

The tool I maintain - SQL Workbench/J - has a command to export tables (or queries) to a wide range of export formats.

WbExport can escape of special characters by using \t, \n etc notation for "control" characters

http://sql-workbench.net/manual/command-export.html

especially

http://sql-workbench.net/manual/command-export.html#text-escape-switch

This can all be done from a shell/batch script if needed.

The tool needs a Java runtime and the Postgres JDBC driver.

Thomas