csv format for psql

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: csv format for psql
Date: 2018-01-30 08:31:29
Message-ID: a8de371e-006f-4f92-ab72-2bbe3ee78f03@manitou-mail.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

* \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.

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

Attachment Content-Type Size
psql-csv-format.patch text/plain 10.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Meskes 2018-01-30 08:31:44 Re: [HACKERS] datetime.h defines like PM conflict with external libraries
Previous Message Michael Paquier 2018-01-30 08:17:13 Re: [HACKERS] generated columns