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
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


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

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.


In response to


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