Re: psql and tab-delimited output

On 09/06/2014 10:34 AM, Abelard Hoffman wrote:
> On Sat, Sep 6, 2014 at 7:28 AM, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@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 guess it depends on what you are using.

In Python:

In [30]: with open('data.tsv', 'rb') as c_file:
     c_reader = csv.reader(c_file, delimiter='\t', quotechar = '"')
     for row in c_reader:
         print row
    ....:
['id', 'fld_1']
['1', 'test\tvalue']
['2', 'test\tvalue']
['3', 'test\tvalue']


In [33]: with open('data.tsv', 'rb') as c_file:
     c_reader = csv.reader(c_file, delimiter='\t', quotechar = '"')
     for row in c_reader:
         print row[1]
    ....:
fld_1
test    value
test    value
test    value

The Postgres docs have a good note on the CSV format:

http://www.postgresql.org/docs/9.3/interactive/sql-copy.html

"Note: Many programs produce strange and occasionally perverse CSV
files, so the file format is more a convention than a standard. Thus you
might encounter some files that cannot be imported using this mechanism,
and COPY might produce files that other programs cannot process."


So it always an adventure:)

>
> 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.
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com