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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(at)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(dot)klaver(at)aklaver(dot)com