Re: csv format for psql

2018-01-30 9:31 GMT+01:00 Daniel Verite <daniel(at)manitou-mail(dot)org>:

> Hi,
>
>
> This patch implements csv as an output format in psql
> (\pset format csv). It's quite similar to the unaligned format,
> except that it applies CSV quoting rules (obviously!) and that
> it prints no footer and no title.
> As with unaligned, a header with column names is output unless
> tuples_only is on. It also supports the fieldsep/fielsep_zero
> and recordsep/recordsep_zero settings.
>
> Most of times, the need for CSV is covered by \copy or COPY with
> the CSV option, but there are some cases where it would be more
> practical to have it as an output format in psql.
>

I absolutely agree

> * \copy does not interpolate psql variables and is a single-line
> command, so making a query fit these contraints can be cumbersome.
> It can be got around by defining a temporary view and
> \copy from that view, but that doesn't work in a read-only context
> such as when connected to a standby.
>
> * the server-side COPY TO STDOUT can also be used from psql,
> typically with psql -c "COPY (query) TO STDOUT CSV" > file.csv,
> but that's too simple to extract multiple result sets per script.
> COPY is also more rigid than psql in the options to delimit
> fields and records.
>
> * copy with csv can't help for the output of meta-commands
> such as \gx, \crosstabview, \l, \d ... whereas a CSV format within psql
> does work with these.
>

It is great - long time I miss this feature - It is interesting for
scripting, ETL, ..

This format is too important, so some special short or long option can be
practical (it will be printed in help)

some like --csv

I found one issue - PostgreSQL default field separator is "|". Maybe good
time to use more common "," ?

Or when field separator was not explicitly defined, then use "," for CSV,
and "|" for other. Although it can be little bit messy

Thank you

Pavel

>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>