Re: csv format for psql - Mailing list pgsql-hackers

Bonjour Michaël,

>> But again COPY is concerned with importing the data that preexists,
>> even if it's weird, whereas a psql output formats are not.
>
> Hm.  I checked the contents of the patch in details which provide output
> consistent with COPY, but after looking at the global picture I am
> getting cold feet on this patch for a couple of reasons:
> - This stuff adds new code paths in the frontend mimicking what the
> backend already does for years, both doing the same thing.

  - Backend's COPY cannot write to client space, so the comparison
    is not relevant.

  - "\copy (SQL-query) TO STDOUT CSV;" is kind of a pain, because one has
    to edit around the query, which is not convenient, esp from the
    command line:

    sh> psql --csv -c 'SELECT 1 as one, 2 as two' > out.csv

    vs

    sh> psql -c "\copy (SELECT 1 AS one, 2 as two) TO STDOUT CSV" > out.csv

    or mixing the output file name inside the argument, which is very
    unshell like:

    sh> psql -c "\copy (SELECT 1 AS one, 2 as two) TO 'out.csv' CSV"

If you have a "query.sql" file that you want to output in csv, there is no 
simple way to do that with \copy/COPY, whereas "psql --csv -f query.sql" 
looks pretty straightforward to me. Also, in a makefile, I could write:

    %.csv: %.sql
        psql --csv -f $< > $@

My point is that \copy, COPY and the proposed CSV format do not address 
the same use cases.

> - There are already three ways to fetch data in this format with COPY,
> \copy and file_fdw, with all three using the same code paths for option
> validations (I can see the arguments at the top of the thread for which
> COPY SELECT can actually do everything you want with?).
> - The experience is confusing, as the psql format uses different options
> than the backend to do the same things:
> -- tuples_only instead of HEADER.
> -- fieldsep_csv instead of DELIMITER
> -- null is an equivalent of the one with the same name, which is
> actually consistent.
> -- encoding is also an equivalent of ENCODING.
> -- and all the others missing.
> That looks like a lot.

I disagree on this one: the proposed csv format just follows the existing 
psql format pattern used for 8 formats and reuses it for csv.

Note that there are existing command line options for tuples_only (-t), 
encoding is inherited from the shell and does not need to be changed that 
often nowadays, fieldsep_csv is kind-of a pain, but then if someone wants 
"comma-separated-values" NOT separated by commas, probably they can handle 
it.

Basically the proposed patch addresses a simple and convenient use case 
which are neither addressed by \copy nor COPY. The fact that more options 
are available with these commands does it precludes its usefulness as is.

-- 
Fabien.