On 09/07/2014 01:45 AM, Abelard Hoffman wrote:
On Sat, Sep 6, 2014 at 11:43 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote: On 09/06/2014 10:34 AM, Abelard Hoffman wrote: On Sat, Sep 6, 2014 at 7:28 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> <mailto:adrian.klaver@aklaver.__com <mailto:adrian.klaver@xxxxxxxxxxx>>> 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: [snip] Yeah, I can parse CSV easily enough. My boss wants TSV though (I could parse CSV and split out TSV, of course). Even then, having to take the report query (which can be big), strip all the newlines and insert it into a \copy command is kind of a PITA.
I see CSV as a generic term that covers all separated value formats, so TSV is just a variation. I am not sure exactly what the end point of all this, so I am probably not going to be able to offer much more. One heads up, in 9.3+ you have the option in \copy(COPY) to use an external program to copy TO or FROM
I also took a look at the psql source. It doesn't look like it would be very hard to add some backslash escaping logic as an option. Am I the only one that would use that? For reports, everyone else mostly uses other tools? I'd like to stay away from GUI-tools, if possible.
Not sure what you are asking for here. Something like this?: http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html 4.1.2.2. String Constants with C-style Escapes test=> select E'a\tb'; ?column? ----------- a b (1 row)
Thanks.
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
