Skip to content

Commit aa2ba50

Browse files
tglsfdctglsfdc
committed
Add CSV table output mode in psql.
"\pset format csv", or --csv, selects comma-separated values table format. This is compliant with RFC 4180, except that we aren't too picky about whether the record separator is LF or CRLF; also, the user may choose a field separator other than comma. This output format is directly compatible with the server's COPY CSV format, and will also be useful as input to other programs. It's considerably safer for that purpose than the old recommendation to use "unaligned" format, since the latter couldn't handle data containing the field separator character. Daniel Vérité, reviewed by Fabien Coelho and David Fetter, some tweaking by me Discussion: https://postgr.es/m/a8de371e-006f-4f92-ab72-2bbe3ee78f03@manitou-mail.org
1 parent 9a98984 commit aa2ba50

File tree

10 files changed

+402
-34
lines changed

10 files changed

+402
-34
lines changed

‎doc/src/sgml/ref/psql-ref.sgml‎

Lines changed: 79 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -68,8 +68,8 @@ PostgreSQL documentation
6868
<listitem>
6969
<para>
7070
Switches to unaligned output mode. (The default output mode is
71-
otherwise aligned.) This is equivalent to <command>\pset format
72-
unaligned</command>.
71+
<literal>aligned</literal>.) This is equivalent to
72+
<command>\pset format unaligned</command>.
7373
</para>
7474
</listitem>
7575
</varlistentry>
@@ -151,6 +151,16 @@ EOF
151151
</listitem>
152152
</varlistentry>
153153

154+
<varlistentry>
155+
<term><option>--csv</option></term>
156+
<listitem>
157+
<para>
158+
Switches to <acronym>CSV</acronym> (Comma-Separated Values) output
159+
mode. This is equivalent to <command>\pset format csv</command>.
160+
</para>
161+
</listitem>
162+
</varlistentry>
163+
154164
<varlistentry>
155165
<term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
156166
<term><option>--dbname=<replaceable class="parameter">dbname</replaceable></option></term>
@@ -270,8 +280,8 @@ EOF
270280
<term><option>--html</option></term>
271281
<listitem>
272282
<para>
273-
Turn on <acronym>HTML</acronym> tabular output. This is
274-
equivalent to <literal>\pset format html</literal> or the
283+
Switches to <acronym>HTML</acronym> output mode. This is
284+
equivalent to <command>\pset format html</command> or the
275285
<command>\H</command> command.
276286
</para>
277287
</listitem>
@@ -2520,6 +2530,19 @@ lo_import 152801
25202530
</listitem>
25212531
</varlistentry>
25222532

2533+
<varlistentry>
2534+
<term><literal>csv_fieldsep</literal></term>
2535+
<listitem>
2536+
<para>
2537+
Specifies the field separator to be used in
2538+
<acronym>CSV</acronym> output format. If the separator character
2539+
appears in a field's value, that field is output within double
2540+
quotes, following standard <acronym>CSV</acronym> rules.
2541+
The default is a comma.
2542+
</para>
2543+
</listitem>
2544+
</varlistentry>
2545+
25232546
<varlistentry>
25242547
<term><literal>expanded</literal> (or <literal>x</literal>)</term>
25252548
<listitem>
@@ -2547,8 +2570,8 @@ lo_import 152801
25472570
<listitem>
25482571
<para>
25492572
Specifies the field separator to be used in unaligned output
2550-
format. That way one can create, for example, tab- or
2551-
comma-separated output, which other programs might prefer. To
2573+
format. That way one can create, for example, tab-separated
2574+
output, which other programs might prefer. To
25522575
set a tab as field separator, type <literal>\pset fieldsep
25532576
'\t'</literal>. The default field separator is
25542577
<literal>'|'</literal> (a vertical bar).
@@ -2585,22 +2608,48 @@ lo_import 152801
25852608
<listitem>
25862609
<para>
25872610
Sets the output format to one of <literal>aligned</literal>,
2588-
<literal>asciidoc</literal>, <literal>html</literal>,
2589-
<literal>latex</literal> (uses <literal>tabular</literal>),
2611+
<literal>asciidoc</literal>,
2612+
<literal>csv</literal>,
2613+
<literal>html</literal>,
2614+
<literal>latex</literal>,
25902615
<literal>latex-longtable</literal>, <literal>troff-ms</literal>,
25912616
<literal>unaligned</literal>, or <literal>wrapped</literal>.
25922617
Unique abbreviations are allowed.
25932618
</para>
25942619

2620+
<para><literal>aligned</literal> format is the standard,
2621+
human-readable, nicely formatted text output; this is the default.
2622+
</para>
2623+
25952624
<para><literal>unaligned</literal> format writes all columns of a row on one
25962625
line, separated by the currently active field separator. This
25972626
is useful for creating output that might be intended to be read
2598-
in by other programs (for example, tab-separated or comma-separated
2599-
format).
2627+
in by other programs, for example, tab-separated or comma-separated
2628+
format. However, the field separator character is not treated
2629+
specially if it appears in a column's value;
2630+
so <acronym>CSV</acronym> format may be better suited for such
2631+
purposes.
26002632
</para>
26012633

2602-
<para><literal>aligned</literal> format is the standard, human-readable,
2603-
nicely formatted text output; this is the default.
2634+
<para><literal>csv</literal> format
2635+
<indexterm>
2636+
<primary>CSV (Comma-Separated Values) format</primary>
2637+
<secondary>in psql</secondary>
2638+
</indexterm>
2639+
writes column values separated by commas, applying the quoting
2640+
rules described in
2641+
<ulink url="https://tools.ietf.org/html/rfc4180">RFC 4180</ulink>.
2642+
This output is compatible with the CSV format of the server's
2643+
<command>COPY</command> command.
2644+
A header line with column names is generated unless
2645+
the <literal>tuples_only</literal> parameter is
2646+
<literal>on</literal>. Titles and footers are not printed.
2647+
Each row is terminated by the system-dependent end-of-line character,
2648+
which is typically a single newline (<literal>\n</literal>) for
2649+
Unix-like systems or a carriage return and newline sequence
2650+
(<literal>\r\n</literal>) for Microsoft Windows.
2651+
Field separator characters other than comma can be selected with
2652+
<command>\pset csv_fieldsep</command>.
26042653
</para>
26052654

26062655
<para><literal>wrapped</literal> format is like <literal>aligned</literal> but wraps
@@ -2620,8 +2669,12 @@ lo_import 152801
26202669
language. They are not complete documents! This might not be
26212670
necessary in <acronym>HTML</acronym>, but in
26222671
<application>LaTeX</application> you must have a complete
2623-
document wrapper. <literal>latex-longtable</literal>
2624-
also requires the <application>LaTeX</application>
2672+
document wrapper.
2673+
The <literal>latex</literal> format
2674+
uses <application>LaTeX</application>'s <literal>tabular</literal>
2675+
environment.
2676+
The <literal>latex-longtable</literal> format
2677+
requires the <application>LaTeX</application>
26252678
<literal>longtable</literal> and <literal>booktabs</literal> packages.
26262679
</para>
26272680
</listitem>
@@ -4588,17 +4641,24 @@ first second
45884641

45894642
peter@localhost testdb=&gt; <userinput>\pset border 1</userinput>
45904643
Border style is 1.
4591-
peter@localhost testdb=&gt; <userinput>\pset format unaligned</userinput>
4592-
Output format is unaligned.
4593-
peter@localhost testdb=&gt; <userinput>\pset fieldsep ","</userinput>
4594-
Field separator is ",".
4644+
peter@localhost testdb=&gt; <userinput>\pset format csv</userinput>
4645+
Output format is csv.
45954646
peter@localhost testdb=&gt; <userinput>\pset tuples_only</userinput>
4596-
Showing only tuples.
4647+
Tuples only is on.
45974648
peter@localhost testdb=&gt; <userinput>SELECT second, first FROM my_table;</userinput>
45984649
one,1
45994650
two,2
46004651
three,3
46014652
four,4
4653+
peter@localhost testdb=&gt; <userinput>\pset format unaligned</userinput>
4654+
Output format is unaligned.
4655+
peter@localhost testdb=&gt; <userinput>\pset fieldsep '\t'</userinput>
4656+
Field separator is " ".
4657+
peter@localhost testdb=&gt; <userinput>SELECT second, first FROM my_table;</userinput>
4658+
one 1
4659+
two 2
4660+
three 3
4661+
four 4
46024662
</programlisting>
46034663
Alternatively, use the short commands:
46044664
<programlisting>

‎src/bin/psql/command.c‎

Lines changed: 36 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1957,8 +1957,8 @@ exec_command_pset(PsqlScanState scan_state, bool active_branch)
19571957

19581958
int i;
19591959
static const char *const my_list[] = {
1960-
"border", "columns", "expanded", "fieldsep", "fieldsep_zero",
1961-
"footer", "format", "linestyle", "null",
1960+
"border", "columns", "csv_fieldsep", "expanded", "fieldsep",
1961+
"fieldsep_zero", "footer", "format", "linestyle", "null",
19621962
"numericlocale", "pager", "pager_min_lines",
19631963
"recordsep", "recordsep_zero",
19641964
"tableattr", "title", "tuples_only",
@@ -3616,6 +3616,9 @@ _align2string(enum printFormat in)
36163616
case PRINT_ASCIIDOC:
36173617
return "asciidoc";
36183618
break;
3619+
case PRINT_CSV:
3620+
return "csv";
3621+
break;
36193622
case PRINT_HTML:
36203623
return "html";
36213624
break;
@@ -3696,6 +3699,7 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
36963699
/* remember to update error message below when adding more */
36973700
{"aligned", PRINT_ALIGNED},
36983701
{"asciidoc", PRINT_ASCIIDOC},
3702+
{"csv", PRINT_CSV},
36993703
{"html", PRINT_HTML},
37003704
{"latex", PRINT_LATEX},
37013705
{"troff-ms", PRINT_TROFF_MS},
@@ -3737,7 +3741,7 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
37373741
}
37383742
else
37393743
{
3740-
psql_error("\\pset: allowed formats are aligned, asciidoc, html, latex, latex-longtable, troff-ms, unaligned, wrapped\n");
3744+
psql_error("\\pset: allowed formats are aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, wrapped\n");
37413745
return false;
37423746
}
37433747
}
@@ -3836,6 +3840,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
38363840
popt->topt.expanded = !popt->topt.expanded;
38373841
}
38383842

3843+
/* field separator for CSV format */
3844+
else if (strcmp(param, "csv_fieldsep") == 0)
3845+
{
3846+
if (value)
3847+
{
3848+
/* CSV separator has to be a one-byte character */
3849+
if (strlen(value) != 1)
3850+
{
3851+
psql_error("\\pset: csv_fieldsep must be a single one-byte character\n");
3852+
return false;
3853+
}
3854+
if (value[0] == '"' || value[0] == '\n' || value[0] == '\r')
3855+
{
3856+
psql_error("\\pset: csv_fieldsep cannot be a double quote, a newline, or a carriage return\n");
3857+
return false;
3858+
}
3859+
popt->topt.csvFieldSep[0] = value[0];
3860+
}
3861+
}
3862+
38393863
/* locale-aware numeric output */
38403864
else if (strcmp(param, "numericlocale") == 0)
38413865
{
@@ -4006,6 +4030,13 @@ printPsetInfo(const char *param, struct printQueryOpt *popt)
40064030
printf(_("Expanded display is off.\n"));
40074031
}
40084032

4033+
/* show field separator for CSV format */
4034+
else if (strcmp(param, "csv_fieldsep") == 0)
4035+
{
4036+
printf(_("Field separator for CSV is \"%s\".\n"),
4037+
popt->topt.csvFieldSep);
4038+
}
4039+
40094040
/* show field separator for unaligned text */
40104041
else if (strcmp(param, "fieldsep") == 0)
40114042
{
@@ -4207,6 +4238,8 @@ pset_value_string(const char *param, struct printQueryOpt *popt)
42074238
return psprintf("%d", popt->topt.border);
42084239
else if (strcmp(param, "columns") == 0)
42094240
return psprintf("%d", popt->topt.columns);
4241+
else if (strcmp(param, "csv_fieldsep") == 0)
4242+
return pset_quoted_string(popt->topt.csvFieldSep);
42104243
else if (strcmp(param, "expanded") == 0)
42114244
return pstrdup(popt->topt.expanded == 2
42124245
? "auto"

‎src/bin/psql/help.c‎

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -68,7 +68,7 @@ usage(unsigned short int pager)
6868
* Keep this line count in sync with the number of lines printed below!
6969
* Use "psql --help=options | wc" to count correctly.
7070
*/
71-
output = PageOutput(61, pager ? &(pset.popt.topt) : NULL);
71+
output = PageOutput(62, pager ? &(pset.popt.topt) : NULL);
7272

7373
fprintf(output, _("psql is the PostgreSQL interactive terminal.\n\n"));
7474
fprintf(output, _("Usage:\n"));
@@ -108,6 +108,7 @@ usage(unsigned short int pager)
108108

109109
fprintf(output, _("\nOutput format options:\n"));
110110
fprintf(output, _(" -A, --no-align unaligned table output mode\n"));
111+
fprintf(output, _(" --csv CSV (Comma-Separated Values) table output mode\n"));
111112
fprintf(output, _(" -F, --field-separator=STRING\n"
112113
" field separator for unaligned output (default: \"%s\")\n"),
113114
DEFAULT_FIELD_SEP);
@@ -167,7 +168,7 @@ slashUsage(unsigned short int pager)
167168
* Use "psql --help=commands | wc" to count correctly. It's okay to count
168169
* the USE_READLINE line even in builds without that.
169170
*/
170-
output = PageOutput(125, pager ? &(pset.popt.topt) : NULL);
171+
output = PageOutput(126, pager ? &(pset.popt.topt) : NULL);
171172

172173
fprintf(output, _("General\n"));
173174
fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n"));
@@ -272,11 +273,12 @@ slashUsage(unsigned short int pager)
272273
fprintf(output, _(" \\H toggle HTML output mode (currently %s)\n"),
273274
ON(pset.popt.topt.format == PRINT_HTML));
274275
fprintf(output, _(" \\pset [NAME [VALUE]] set table output option\n"
275-
" (NAME := {border|columns|expanded|fieldsep|fieldsep_zero|\n"
276-
" footer|format|linestyle|null|numericlocale|pager|\n"
277-
" pager_min_lines|recordsep|recordsep_zero|tableattr|title|\n"
278-
" tuples_only|unicode_border_linestyle|\n"
279-
" unicode_column_linestyle|unicode_header_linestyle})\n"));
276+
" (border|columns|csv_fieldsep|expanded|fieldsep|\n"
277+
" fieldsep_zero|footer|format|linestyle|null|\n"
278+
" numericlocale|pager|pager_min_lines|recordsep|\n"
279+
" recordsep_zero|tableattr|title|tuples_only|\n"
280+
" unicode_border_linestyle|unicode_column_linestyle|\n"
281+
" unicode_header_linestyle)\n"));
280282
fprintf(output, _(" \\t [on|off] show only rows (currently %s)\n"),
281283
ON(pset.popt.topt.tuples_only));
282284
fprintf(output, _(" \\T [STRING] set HTML <table> tag attributes, or unset if none\n"));

‎src/bin/psql/settings.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@
1212
#include "variables.h"
1313
#include "fe_utils/print.h"
1414

15+
#define DEFAULT_CSV_FIELD_SEP ','
1516
#define DEFAULT_FIELD_SEP "|"
1617
#define DEFAULT_RECORD_SEP "\n"
1718

‎src/bin/psql/startup.c‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -144,6 +144,9 @@ main(int argc, char *argv[])
144144
pset.popt.topt.stop_table = true;
145145
pset.popt.topt.default_footer = true;
146146

147+
pset.popt.topt.csvFieldSep[0] = DEFAULT_CSV_FIELD_SEP;
148+
pset.popt.topt.csvFieldSep[1] = '\0';
149+
147150
pset.popt.topt.unicode_border_linestyle = UNICODE_LINESTYLE_SINGLE;
148151
pset.popt.topt.unicode_column_linestyle = UNICODE_LINESTYLE_SINGLE;
149152
pset.popt.topt.unicode_header_linestyle = UNICODE_LINESTYLE_SINGLE;
@@ -468,6 +471,7 @@ parse_psql_options(int argc, char *argv[], struct adhoc_opts *options)
468471
{"expanded", no_argument, NULL, 'x'},
469472
{"no-psqlrc", no_argument, NULL, 'X'},
470473
{"help", optional_argument, NULL, 1},
474+
{"csv", no_argument, NULL, 2},
471475
{NULL, 0, NULL, 0}
472476
};
473477

@@ -658,6 +662,9 @@ parse_psql_options(int argc, char *argv[], struct adhoc_opts *options)
658662
exit(EXIT_SUCCESS);
659663
}
660664
break;
665+
case 2:
666+
pset.popt.topt.format = PRINT_CSV;
667+
break;
661668
default:
662669
unknown_option:
663670
fprintf(stderr, _("Try \"%s --help\" for more information.\n"),

‎src/bin/psql/tab-complete.c‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2605,6 +2605,7 @@ psql_completion(const char *text, int start, int end)
26052605
/* Complete CREATE EVENT TRIGGER <name> ON with event_type */
26062606
else if (Matches("CREATE", "EVENT", "TRIGGER", MatchAny, "ON"))
26072607
COMPLETE_WITH("ddl_command_start", "ddl_command_end", "sql_drop");
2608+
26082609
/*
26092610
* Complete CREATE EVENT TRIGGER <name> ON <event_type>. EXECUTE FUNCTION
26102611
* is the recommended grammar instead of EXECUTE PROCEDURE in version 11
@@ -3524,7 +3525,7 @@ psql_completion(const char *text, int start, int end)
35243525
else if (TailMatchesCS("\\password"))
35253526
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
35263527
else if (TailMatchesCS("\\pset"))
3527-
COMPLETE_WITH_CS("border", "columns", "expanded",
3528+
COMPLETE_WITH_CS("border", "columns", "csv_fieldsep", "expanded",
35283529
"fieldsep", "fieldsep_zero", "footer", "format",
35293530
"linestyle", "null", "numericlocale",
35303531
"pager", "pager_min_lines",
@@ -3536,7 +3537,7 @@ psql_completion(const char *text, int start, int end)
35363537
else if (TailMatchesCS("\\pset", MatchAny))
35373538
{
35383539
if (TailMatchesCS("format"))
3539-
COMPLETE_WITH_CS("aligned", "asciidoc", "html", "latex",
3540+
COMPLETE_WITH_CS("aligned", "asciidoc", "csv", "html", "latex",
35403541
"latex-longtable", "troff-ms", "unaligned",
35413542
"wrapped");
35423543
else if (TailMatchesCS("linestyle"))

0 commit comments

Comments
 (0)