Re: csv format for psql

2018-04-07 14:23 GMT+02:00 Daniel Verite <daniel@manitou-mail.org>:

        Peter Eisentraut wrote:

> Another thought: Isn't CSV just the same as unaligned output plus some
> quoting?  Could we add a quote character setting and then define --csv
> to be quote-character = " and fieldsep = , ?

Plus footer set to off. So --csv would be like
\pset format unaligned
\pset fieldsep ','
\pset footer off
\pset unaligned_quote_character '"'

I guess we'd add a \csv command that does these together.

There would still be some discomfort with some of the issues
discussed upthread. For instance
  psql -F ';' --csv
is likely to reset fieldsep to ',' having then a different outcome than
  psql --csv -F';'

And there's the point on how to come back from \csv to another
output, given that it has overwritten "footer" that is used across
all formats, and fieldsep used by unaligned.
So a user might need to figure out anyway the
intricaties behind \csv, if it's not an independant format.

On the whole I'm inclined to resubmit the patch with
fieldsep_csv and some minor changes based on the rest
of the discussion.

Can we restart discussion about this feature? I am sure so there is strong common agreement about usability of this feature. Now, we have good enough time to design and implement it well.

I am starting this discussion with some initial requirement and we can talk about validity of these requirements first.

1. command line long option --csv set output format CSV, default separator should be ','

2. option -F should to work too (there is (was) first issue - this value is now semicolon). I don't like introduction a new like "-F" option just for CSV, because we can share this option for all output formats where separator can be customized.

3. The behave should not depend on order of usage

4. I miss some short/long option for enabling/disabling header (default should be on), there is option -t, that is inverse value of "use header". I am not sure if it is enough.

5. In interactive mode I prefer activating CSV format via \pset format csv .. like almost all any other supported formats

It should to set output configuration like mentioned

\pset format unaligned
\pset fieldsep ',' -- if not assigned before
\pset footer off
\pset unaligned_quote_character '"'

similarly like command line I am expecting so I can set \pset fieldsep and \pset format in any order.

expected behave:

psql -c "select ..." --csv -F';'

psql -c "select ..." -F';' --csv

interactive

\pset format csv

\pset fieldsep ;

or

\pset fieldsep ;

\pset format csv

I don't like when one command overwrite settings of some other command. We can introduce some similar like GUC where default values from configure file can be overwritten by custom setting for session. So I am able to thinking about some settings

like

\pset csv_default_separator

\pset csv_default_header

Then there is question to simplify all and use \pset csv_separator, and csv format independent of fieldseparator value? It is possible, but I don't think so have more option for similar value is good idea (for interactive mode).

Why I propose these rich and strong requirements?

1. CSV is fundamental format - I don't know any other with similar value for data import/export

2. unfortunately, important feature (separator) is not same in all areas - in Czech rep most used separator is semicolon - so typical case needs setting format and setting separator.

Regards

Pavel