Re: csv format for psql

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Michael Paquier <michael(at)paquier(dot)xyz>
Cc: Daniel Verite <daniel(at)manitou-mail(dot)org>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: csv format for psql
Date: 2018-11-14 12:17:19
Message-ID: alpine.DEB.2.21.1811131319240.14630@lancre
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Raúl Marín Rodríguez 2018-11-14 12:46:34 [PATCH] Memory leak in pg_config
Previous Message Joshua Yanovski 2018-11-14 11:52:27 Re: In-place updates and serializable transactions