Add \pset options for boolean value display
Hi!
Please accept this patch (though it's not finished, just functional)!
It's \pset null for boolean values
Printing tables of 't' and 'f' makes for painful-to-read output.
This provides an easy win for psql users, giving them the option to do
better. I would like all of our documentation examples eventually to be
done with "\pset display_true true" and "\pset display_false false"
configured. Getting it into v18 so docs being written now, like my NULL
patch, can make use of it, would make my year.
I was initially going to go with the following to mirror null even more
closely.
\pset { true | false } value
And still like that option, though having the same word repeated as the
expected value and name hurts it a bit.
This next one was also considered but the word "print" already seemed a bit
too entwined with \pset format related stuff.
\pset { print_true | print_false } value
David J.
Attachments:
v0-0001-Add-pset-options-for-boolean-value-display.patchtext/x-patch; charset=US-ASCII; name=v0-0001-Add-pset-options-for-boolean-value-display.patchDownload
From 4d306939c008ecf7e8ae0036a46218b059a069c7 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Thu, 20 Mar 2025 20:03:01 -0700
Subject: [PATCH] Add \pset options for boolean value display
The server's space-expedient choice to use 't' and 'f' to represent
boolean true and false respectively is technically understandable
but visually atrocious. Teach psql to detect these two values
and print whatever it deems is appropriate. For now, in the
interest of backward compatability, that defaults to 't' and 'f'.
However, now the user can impose their own standards by using the
newly introduced display_true and display_false pset settings.
---
src/bin/psql/command.c | 43 ++++++++++++++++++++++++++++++++++++
src/fe_utils/print.c | 6 +++++
src/include/fe_utils/print.h | 2 ++
3 files changed, 51 insertions(+)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index bbe337780f..29f212e54f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -2688,6 +2688,7 @@ exec_command_pset(PsqlScanState scan_state, bool active_branch)
"border", "columns", "csv_fieldsep", "expanded", "fieldsep",
"fieldsep_zero", "footer", "format", "linestyle", "null",
"numericlocale", "pager", "pager_min_lines",
+ "display_true", "display_false",
"recordsep", "recordsep_zero",
"tableattr", "title", "tuples_only",
"unicode_border_linestyle",
@@ -5193,6 +5194,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
}
}
+ /* true display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ if (value)
+ {
+ free(popt->truePrint);
+ popt->truePrint = pg_strdup(value);
+ }
+ }
+
+ /* false display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ if (value)
+ {
+ free(popt->falsePrint);
+ popt->falsePrint = pg_strdup(value);
+ }
+ }
+
/* field separator for unaligned text */
else if (strcmp(param, "fieldsep") == 0)
{
@@ -5411,6 +5432,20 @@ printPsetInfo(const char *param, printQueryOpt *popt)
popt->nullPrint ? popt->nullPrint : "");
}
+ /* show boolean true display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ printf(_("Boolean true display is \"%s\".\n"),
+ popt->truePrint ? popt->truePrint : "t");
+ }
+
+ /* show boolean false display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ printf(_("Boolean false display is \"%s\".\n"),
+ popt->falsePrint ? popt->falsePrint : "f");
+ }
+
/* show locale-aware numeric output */
else if (strcmp(param, "numericlocale") == 0)
{
@@ -5656,6 +5691,14 @@ pset_value_string(const char *param, printQueryOpt *popt)
return pset_quoted_string(popt->nullPrint
? popt->nullPrint
: "");
+ else if (strcmp(param, "display_true") == 0)
+ return pset_quoted_string(popt->truePrint
+ ? popt->truePrint
+ : "t");
+ else if (strcmp(param, "display_false") == 0)
+ return pset_quoted_string(popt->falsePrint
+ ? popt->falsePrint
+ : "f");
else if (strcmp(param, "numericlocale") == 0)
return pstrdup(pset_bool_string(popt->topt.numericLocale));
else if (strcmp(param, "pager") == 0)
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index 5e5e54e1b7..2156616c9d 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -3582,6 +3582,12 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
if (PQgetisnull(result, r, c))
cell = opt->nullPrint ? opt->nullPrint : "";
+ else if (PQftype(result, c) == BOOLOID)
+ {
+ cell = (PQgetvalue(result, r, c)[0] == 't')
+ ? (opt->truePrint ? opt->truePrint : "t")
+ : (opt->falsePrint ? opt->falsePrint : "f");
+ }
else
{
cell = PQgetvalue(result, r, c);
diff --git a/src/include/fe_utils/print.h b/src/include/fe_utils/print.h
index c99c2ee1a3..2378e26acb 100644
--- a/src/include/fe_utils/print.h
+++ b/src/include/fe_utils/print.h
@@ -184,6 +184,8 @@ typedef struct printQueryOpt
{
printTableOpt topt; /* the options above */
char *nullPrint; /* how to print null entities */
+ char *truePrint; /* how to print boolean true values */
+ char *falsePrint; /* how to print boolean false values */
char *title; /* override title */
char **footers; /* override footer (default is "(xx rows)") */
bool translate_header; /* do gettext on column headers */
--
2.34.1
On Thu, Mar 20, 2025 at 8:24 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
It's \pset null for boolean values
v1, Ready aside from bike-shedding the name.
David J.
Attachments:
v1-0001-Add-pset-options-for-boolean-value-display.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Add-pset-options-for-boolean-value-display.patchDownload
From c897e53577d433c374e51619405a5c2b8bd4151c Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
Date: Wed, 18 Jun 2025 12:20:43 -0700
Subject: [PATCH] Add \pset options for boolean value display
The server's space-expedient choice to use 't' and 'f' to represent
boolean true and false respectively is technically understandable
but visually atrocious. Teach psql to detect these two values
and print whatever it deems is appropriate. For now, in the
interest of backward compatability, that defaults to 't' and 'f'.
However, now the user can impose their own standards by using the
newly introduced display_true and display_false pset settings.
---
doc/src/sgml/ref/psql-ref.sgml | 24 ++++++++++++++++
src/bin/psql/command.c | 45 +++++++++++++++++++++++++++++-
src/fe_utils/print.c | 6 ++++
src/include/fe_utils/print.h | 2 ++
src/test/regress/expected/psql.out | 32 +++++++++++++++++++++
src/test/regress/sql/psql.sql | 16 +++++++++++
6 files changed, 124 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 570ef21d1fc..03d4e429d8a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3287,6 +3287,30 @@ SELECT $1 \parse stmt1
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-pset-display_true">
+ <term><literal>display_true</literal></term>
+ <listitem>
+ <para>
+ Sets the string to be printed in place of a true value.
+ The default is to print <literal>t</literal> as that is the value
+ transmitted by the server. For readability,
+ <literal>\pset print_true 'true'</literal> is recommended.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="app-psql-meta-command-pset-display_false">
+ <term><literal>display_false</literal></term>
+ <listitem>
+ <para>
+ Sets the string to be printed in place of a false value.
+ The default is to print <literal>f</literal> as that is the value
+ transmitted by the server. For readability,
+ <literal>\pset print_false 'false'</literal> is recommended.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="app-psql-meta-command-pset-null">
<term><literal>null</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 83e84a77841..c6afa982a59 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -2688,7 +2688,8 @@ exec_command_pset(PsqlScanState scan_state, bool active_branch)
int i;
static const char *const my_list[] = {
- "border", "columns", "csv_fieldsep", "expanded", "fieldsep",
+ "border", "columns", "csv_fieldsep",
+ "display_true", "display_false", "expanded", "fieldsep",
"fieldsep_zero", "footer", "format", "linestyle", "null",
"numericlocale", "pager", "pager_min_lines",
"recordsep", "recordsep_zero",
@@ -5198,6 +5199,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
}
}
+ /* true display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ if (value)
+ {
+ free(popt->truePrint);
+ popt->truePrint = pg_strdup(value);
+ }
+ }
+
+ /* false display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ if (value)
+ {
+ free(popt->falsePrint);
+ popt->falsePrint = pg_strdup(value);
+ }
+ }
+
/* field separator for unaligned text */
else if (strcmp(param, "fieldsep") == 0)
{
@@ -5416,6 +5437,20 @@ printPsetInfo(const char *param, printQueryOpt *popt)
popt->nullPrint ? popt->nullPrint : "");
}
+ /* show boolean true display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ printf(_("Boolean true display is \"%s\".\n"),
+ popt->truePrint ? popt->truePrint : "t");
+ }
+
+ /* show boolean false display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ printf(_("Boolean false display is \"%s\".\n"),
+ popt->falsePrint ? popt->falsePrint : "f");
+ }
+
/* show locale-aware numeric output */
else if (strcmp(param, "numericlocale") == 0)
{
@@ -5661,6 +5696,14 @@ pset_value_string(const char *param, printQueryOpt *popt)
return pset_quoted_string(popt->nullPrint
? popt->nullPrint
: "");
+ else if (strcmp(param, "display_true") == 0)
+ return pset_quoted_string(popt->truePrint
+ ? popt->truePrint
+ : "t");
+ else if (strcmp(param, "display_false") == 0)
+ return pset_quoted_string(popt->falsePrint
+ ? popt->falsePrint
+ : "f");
else if (strcmp(param, "numericlocale") == 0)
return pstrdup(pset_bool_string(popt->topt.numericLocale));
else if (strcmp(param, "pager") == 0)
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index 4af0f32f2fc..7829460f7d3 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -3582,6 +3582,12 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
if (PQgetisnull(result, r, c))
cell = opt->nullPrint ? opt->nullPrint : "";
+ else if (PQftype(result, c) == BOOLOID)
+ {
+ cell = (PQgetvalue(result, r, c)[0] == 't')
+ ? (opt->truePrint ? opt->truePrint : "t")
+ : (opt->falsePrint ? opt->falsePrint : "f");
+ }
else
{
cell = PQgetvalue(result, r, c);
diff --git a/src/include/fe_utils/print.h b/src/include/fe_utils/print.h
index c99c2ee1a31..2378e26acb6 100644
--- a/src/include/fe_utils/print.h
+++ b/src/include/fe_utils/print.h
@@ -184,6 +184,8 @@ typedef struct printQueryOpt
{
printTableOpt topt; /* the options above */
char *nullPrint; /* how to print null entities */
+ char *truePrint; /* how to print boolean true values */
+ char *falsePrint; /* how to print boolean false values */
char *title; /* override title */
char **footers; /* override footer (default is "(xx rows)") */
bool translate_header; /* do gettext on column headers */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index cf48ae6d0c2..a276d79bf46 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -445,6 +445,8 @@ environment value
border 1
columns 0
csv_fieldsep ','
+display_true 't'
+display_false 'f'
expanded off
fieldsep '|'
fieldsep_zero off
@@ -464,6 +466,36 @@ unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single
xheader_width full
+-- test the simple display substitution settings
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
+execute q;
+ n | t | f
+--------+------+-------
+ (null) | true | false
+(1 row)
+
+\pset null
+\pset display_true
+\pset display_false
+execute q;
+ n | t | f
+--------+------+-------
+ (null) | true | false
+(1 row)
+
+\pset null ''
+\pset display_true 't'
+\pset display_false 'f'
+execute q;
+ n | t | f
+---+---+---
+ | t | f
+(1 row)
+
+deallocate q;
-- test multi-line headers, wrapping, and newline indicators
-- in aligned, unaligned, and wrapped formats
prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 1a8a83462f0..c1784a691fe 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -219,6 +219,22 @@ select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
-- show all pset options
\pset
+-- test the simple display substitution settings
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
+execute q;
+\pset null
+\pset display_true
+\pset display_false
+execute q;
+\pset null ''
+\pset display_true 't'
+\pset display_false 'f'
+execute q;
+deallocate q;
+
-- test multi-line headers, wrapping, and newline indicators
-- in aligned, unaligned, and wrapped formats
prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab
--
2.34.1
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Thu, Mar 20, 2025 at 8:24 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:It's \pset null for boolean values
v1, Ready aside from bike-shedding the name.
Do we really want this? It's the sort of thing that has a strong
potential to break anything that reads psql output --- and I'd
urge you to think that human consumers of psql output may well
be the minority. There's an awful lot of scripts out there.
I concede that \pset null hasn't had a huge amount of pushback,
but that doesn't mean that making boolean output unpredictable
will be cost-free. And the costs won't be paid by you (or me),
but by people who didn't ask for it.
regards, tom lane
On Tue, Jun 24, 2025 at 3:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Thu, Mar 20, 2025 at 8:24 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:It's \pset null for boolean values
v1, Ready aside from bike-shedding the name.
Do we really want this? It's the sort of thing that has a strong
potential to break anything that reads psql output --- and I'd
urge you to think that human consumers of psql output may well
be the minority. There's an awful lot of scripts out there.I concede that \pset null hasn't had a huge amount of pushback,
but that doesn't mean that making boolean output unpredictable
will be cost-free. And the costs won't be paid by you (or me),
but by people who didn't ask for it.
If we didn't use psql to produce all of our examples I'd be a bit more
accepting of this position. Yes, users of it need to do so responsibly.
But we have tons of pretty-presentation-oriented options in psql so, yes, I
do believe this is well within its charter.
David J.
David G. Johnston wrote:
It's \pset null for boolean values
v1, Ready aside from bike-shedding the name.
An annoying weakness of this approach is that it cannot detect
booleans inside arrays or composite types or COPY output,
meaning that the translation of t/f is incomplete.
Also it reminds of a previous discussion (see [1]/messages/by-id/56308F56.8060908@joh.to) where pretty much
the same idea was proposed (and eventually rejected at the time).
[1]: /messages/by-id/56308F56.8060908@joh.to
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
On Wed, Jun 25, 2025 at 11:03 AM Daniel Verite <daniel@manitou-mail.org>
wrote:
David G. Johnston wrote:
It's \pset null for boolean values
v1, Ready aside from bike-shedding the name.
An annoying weakness of this approach is that it cannot detect
booleans inside arrays or composite types
Arrays are probably doable. The low volume of composite literal outputs is
not worth worrying about.
or COPY output,
meaning that the translation of t/f is incomplete.
pset doesn't affect COPY output ever so this doesn't seem problematic.
Also it reminds of a previous discussion (see [1]) where pretty much
the same idea was proposed (and eventually rejected at the time).
Ok, so yes, I really want this hack in psql. It fits with pset formats and
affects our \d and other table-producing meta-commands. Plus I'd like to
use it for documentation examples.
Maybe that's enough to change some decade-old opinions. Mine's apparently
changed since then.
David J.
On 25/06/2025 00:30, Tom Lane wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Thu, Mar 20, 2025 at 8:24 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:It's \pset null for boolean values
Do we really want this?
Yes, many of us do.
It's the sort of thing that has a strong
potential to break anything that reads psql output --- and I'd
urge you to think that human consumers of psql output may well
be the minority. There's an awful lot of scripts out there.
You mean scripts that don't use --no-psqlrc? Those scripts are already
bug ridden.
--
Vik Fearing
On 2025-Jun-24, David G. Johnston wrote:
v1, Ready aside from bike-shedding the name.
Here's v2 after some kibitzing. What do you think?
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Attachments:
v2-0001-Add-pset-options-for-boolean-value-display.patchtext/x-diff; charset=utf-8Download
From ab14c69835836ff70c7193ff016e683ec8de9608 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Mon, 20 Oct 2025 12:29:34 +0200
Subject: [PATCH v2] Add \pset options for boolean value display
The server's space-expedient choice to use 't' and 'f' to represent
boolean true and false respectively is technically understandable but
visually atrocious. Teach psql to detect these two values and print
whatever it deems is appropriate. In the interest of backward
compatability, that defaults to 't' and 'f'. However, now the user can
impose their own standards by using the newly introduced display_true
and display_false pset settings.
Author: David G. Johnston <David.G.Johnston@gmail.com>
Discussion: https://postgr.es/m/CAKFQuwYts3vnfQ5AoKhEaKMTNMfJ443MW2kFswKwzn7fiofkrw@mail.gmail.com
---
doc/src/sgml/ref/psql-ref.sgml | 24 +++++++++++++++++
src/bin/psql/command.c | 43 +++++++++++++++++++++++++++++-
src/fe_utils/print.c | 4 +++
src/include/fe_utils/print.h | 2 ++
src/test/regress/expected/psql.out | 32 ++++++++++++++++++++++
src/test/regress/sql/psql.sql | 16 +++++++++++
6 files changed, 120 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 1a339600bc4..06f1e08d87a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3099,6 +3099,30 @@ SELECT $1 \parse stmt1
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-pset-display-false">
+ <term><literal>display_false</literal></term>
+ <listitem>
+ <para>
+ Sets the string to be printed in place of a false value.
+ The default is to print <literal>f</literal>, as that is the value
+ transmitted by the server. For readability,
+ <literal>\pset display_false 'false'</literal> is recommended.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="app-psql-meta-command-pset-display-true">
+ <term><literal>display_true</literal></term>
+ <listitem>
+ <para>
+ Sets the string to be printed in place of a true value.
+ The default is to print <literal>t</literal>, as that is the value
+ transmitted by the server. For readability,
+ <literal>\pset display_true 'true'</literal> is recommended.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="app-psql-meta-command-pset-expanded">
<term><literal>expanded</literal> (or <literal>x</literal>)</term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index cc602087db2..f7454daf6ed 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -2709,7 +2709,8 @@ exec_command_pset(PsqlScanState scan_state, bool active_branch)
int i;
static const char *const my_list[] = {
- "border", "columns", "csv_fieldsep", "expanded", "fieldsep",
+ "border", "columns", "csv_fieldsep",
+ "display_false", "display_true", "expanded", "fieldsep",
"fieldsep_zero", "footer", "format", "linestyle", "null",
"numericlocale", "pager", "pager_min_lines",
"recordsep", "recordsep_zero",
@@ -5300,6 +5301,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
}
}
+ /* 'false' display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ if (value)
+ {
+ free(popt->falsePrint);
+ popt->falsePrint = pg_strdup(value);
+ }
+ }
+
+ /* 'true' display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ if (value)
+ {
+ free(popt->truePrint);
+ popt->truePrint = pg_strdup(value);
+ }
+ }
+
/* field separator for unaligned text */
else if (strcmp(param, "fieldsep") == 0)
{
@@ -5474,6 +5495,20 @@ printPsetInfo(const char *param, printQueryOpt *popt)
popt->topt.csvFieldSep);
}
+ /* show boolean 'false' display */
+ else if (strcmp(param, "display_false") == 0)
+ {
+ printf(_("Boolean false display is \"%s\".\n"),
+ popt->falsePrint ? popt->falsePrint : "f");
+ }
+
+ /* show boolean 'true' display */
+ else if (strcmp(param, "display_true") == 0)
+ {
+ printf(_("Boolean true display is \"%s\".\n"),
+ popt->truePrint ? popt->truePrint : "t");
+ }
+
/* show field separator for unaligned text */
else if (strcmp(param, "fieldsep") == 0)
{
@@ -5743,6 +5778,12 @@ pset_value_string(const char *param, printQueryOpt *popt)
return psprintf("%d", popt->topt.columns);
else if (strcmp(param, "csv_fieldsep") == 0)
return pset_quoted_string(popt->topt.csvFieldSep);
+ else if (strcmp(param, "display_false") == 0)
+ return pset_quoted_string(popt->falsePrint ?
+ popt->falsePrint : "f");
+ else if (strcmp(param, "display_true") == 0)
+ return pset_quoted_string(popt->truePrint ?
+ popt->truePrint : "t");
else if (strcmp(param, "expanded") == 0)
return pstrdup(popt->topt.expanded == 2
? "auto"
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index 73847d3d6b3..4d97ad2ddeb 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -3775,6 +3775,10 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
if (PQgetisnull(result, r, c))
cell = opt->nullPrint ? opt->nullPrint : "";
+ else if (PQftype(result, c) == BOOLOID)
+ cell = (PQgetvalue(result, r, c)[0] == 't' ?
+ (opt->truePrint ? opt->truePrint : "t") :
+ (opt->falsePrint ? opt->falsePrint : "f"));
else
{
cell = PQgetvalue(result, r, c);
diff --git a/src/include/fe_utils/print.h b/src/include/fe_utils/print.h
index c99c2ee1a31..6a6fc7e132c 100644
--- a/src/include/fe_utils/print.h
+++ b/src/include/fe_utils/print.h
@@ -184,6 +184,8 @@ typedef struct printQueryOpt
{
printTableOpt topt; /* the options above */
char *nullPrint; /* how to print null entities */
+ char *truePrint; /* how to print boolean true values */
+ char *falsePrint; /* how to print boolean false values */
char *title; /* override title */
char **footers; /* override footer (default is "(xx rows)") */
bool translate_header; /* do gettext on column headers */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index fa8984ffe0d..c8f3932edf0 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -445,6 +445,8 @@ environment value
border 1
columns 0
csv_fieldsep ','
+display_false 'f'
+display_true 't'
expanded off
fieldsep '|'
fieldsep_zero off
@@ -464,6 +466,36 @@ unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single
xheader_width full
+-- test the simple display substitution settings
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
+execute q;
+ n | t | f
+--------+------+-------
+ (null) | true | false
+(1 row)
+
+\pset null
+\pset display_true
+\pset display_false
+execute q;
+ n | t | f
+--------+------+-------
+ (null) | true | false
+(1 row)
+
+\pset null ''
+\pset display_true 't'
+\pset display_false 'f'
+execute q;
+ n | t | f
+---+---+---
+ | t | f
+(1 row)
+
+deallocate q;
-- test multi-line headers, wrapping, and newline indicators
-- in aligned, unaligned, and wrapped formats
prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index f064e4f5456..dcdbd4fc020 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -219,6 +219,22 @@ select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
-- show all pset options
\pset
+-- test the simple display substitution settings
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
+execute q;
+\pset null
+\pset display_true
+\pset display_false
+execute q;
+\pset null ''
+\pset display_true 't'
+\pset display_false 'f'
+execute q;
+deallocate q;
+
-- test multi-line headers, wrapping, and newline indicators
-- in aligned, unaligned, and wrapped formats
prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab
--
2.47.3
On Monday, October 20, 2025, Álvaro Herrera <alvherre@kurilemu.de> wrote:
On 2025-Jun-24, David G. Johnston wrote:
v1, Ready aside from bike-shedding the name.
Here's v2 after some kibitzing. What do you think?
Thank you. Seems good from a quick read. I’m regretting the choice of the
display_ prefix; is there any technical limitation or other opposition to
using just true and false?
\pset true ‘true’
\pset false ‘false’
To keep in line with:
\pset null ‘(null)’
David J.
On 2025-Oct-20, David G. Johnston wrote:
Thank you. Seems good from a quick read. I’m regretting the choice of the
display_ prefix; is there any technical limitation or other opposition to
using just true and false?\pset true ‘true’
\pset false ‘false’To keep in line with:
\pset null ‘(null)’
Uhm. I don't know. No technical limitation AFAICS. It looks a bit
weird to me, because those names are so generic; but also I cannot
really object to them. That said, such a last-minute bikeshed comment
seems like a perfect way to kill your patch.
I'll gladly take a vote.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"On the other flipper, one wrong move and we're Fatal Exceptions"
(T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/)
On Oct 21, 2025, at 04:51, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, October 20, 2025, Álvaro Herrera <alvherre@kurilemu.de> wrote:
On 2025-Jun-24, David G. Johnston wrote:v1, Ready aside from bike-shedding the name.
Here's v2 after some kibitzing. What do you think?
Thank you. Seems good from a quick read. I’m regretting the choice of the display_ prefix; is there any technical limitation or other opposition to using just true and false?
\pset true ‘true’
\pset false ‘false’To keep in line with:
\pset null ‘(null)’
+1. Especially, when I see the newly added test case:
```
+prepare q as select null as n, true as t, false as f;
+\pset null '(null)'
+\pset display_true 'true'
+\pset display_false 'false'
```Looks inconsistant. If we decided to use “display_xx” then we should have renamed “null” to “display_null”.
The other thing I am thinking is that, with this patch, users are allowed to display arbitrary strings for true/false, if a user mistakenly set display_true to f and display_false to t, which will load to misunderstanding.
```
evantest=# \pset display_true f
Boolean true display is "f".
evantest=# \pset display_false t
Boolean false display is "t".
evantest=# select true as t, false as f;
t | f
---+---
f | t
(1 row)
```
Can we perform a basic sanity check to prevent this kind of error-prone behavior? The consideration applies to the “null” option, but since “null” lacks a clear opposite string representation (unlike “true”/“t" and “false”/“f”), it’s fine to skip the check for it.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Monday, October 20, 2025, Chao Li <li.evan.chao@gmail.com> wrote:
The other thing I am thinking is that, with this patch, users are allowed
to display arbitrary strings for true/false, if a user mistakenly set
display_true to f and display_false to t, which will load to
misunderstanding.
Sympathetic to the concern but opposed to taking on such responsibility.
They could probably modify their own query to do that if they really wanted
to fool someone and I’m having trouble accepting this happening by
accident. Do we test for yes/no; oui/non (i.e., foreign language choices);
checkmark/X?
David J.
On Monday, October 20, 2025, David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Monday, October 20, 2025, Chao Li <li.evan.chao@gmail.com> wrote:
The other thing I am thinking is that, with this patch, users are allowed
to display arbitrary strings for true/false, if a user mistakenly set
display_true to f and display_false to t, which will load to
misunderstanding.Sympathetic to the concern but opposed to taking on such responsibility.
They could probably modify their own query to do that if they really wanted
to fool someone and I’m having trouble accepting this happening by
accident. Do we test for yes/no; oui/non (i.e., foreign language choices);
checkmark/X?
Actually, preventing t/f makes sense to me. Prevents a “hacker” from
messing with the default outputs in a hard-to-identify manner. Any other
value would point to pset being used.
David J.
On Oct 21, 2025, at 10:29, David G. Johnston <david.g.johnston@gmail.com> wrote:
They could probably modify their own query to do that if they really wanted to fool someone and I’m having trouble accepting this happening by accident.
If they modify queries, the result can visibly correlate to the query, for example:
```
evantest=# select CASE WHEN TRUE THEN 'f' END as t;
t
---
f
(1 row)
```
There is no confusion. But if a user did some test by setting “display_true = f” previous and forget about it, there is a no any indication in current SQL statement but unexpected results might be shown.
Do we test for yes/no; oui/non (i.e., foreign language choices); checkmark/X?
When I said “basic sanity check”, I only meant something like “display_true” cannot be “false” and “f”.
I won’t argue more. It’s also reasonable to let users take own responsibilities to stay away from wrong behavior.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Monday, October 20, 2025, David G. Johnston <david.g.johnston@gmail.com>
wrote:Sympathetic to the concern but opposed to taking on such responsibility.
They could probably modify their own query to do that if they really wanted
to fool someone and I’m having trouble accepting this happening by
accident. Do we test for yes/no; oui/non (i.e., foreign language choices);
checkmark/X?
Actually, preventing t/f makes sense to me. Prevents a “hacker” from
messing with the default outputs in a hard-to-identify manner. Any other
value would point to pset being used.
-1. Yeah, you could reject "\pset true 'f'", but what about
not-obviously-different values such as 'f ', or f with a non-breaking
space, or f with a tab, or yadda yadda yadda?
I went on record as opposed to this entire idea back at the start of
this thread, precisely because I was worried that it could lead to
confusion. And I remain of the opinion that it's not a great idea.
But if we're going to do it, let's not bother with any fig-leaf
proposals that pretend to partially guard against confusion.
regards, tom lane
út 21. 10. 2025 v 9:38 odesílatel Álvaro Herrera <alvherre@kurilemu.de>
napsal:
On 2025-Oct-20, David G. Johnston wrote:
Thank you. Seems good from a quick read. I’m regretting the choice of
the
display_ prefix; is there any technical limitation or other opposition to
using just true and false?\pset true ‘true’
\pset false ‘false’To keep in line with:
\pset null ‘(null)’
Uhm. I don't know. No technical limitation AFAICS. It looks a bit
weird to me, because those names are so generic; but also I cannot
really object to them. That said, such a last-minute bikeshed comment
seems like a perfect way to kill your patch.
I'll gladly take a vote.
I think so this is little bit different case
In this context I see three "safe" variants like
short: t, f
long: true, false
localized: nepravda, pravda (if this is available)
localized short is probably very messy - like 'n' and 'p' for Czech
language and never be used
In the Czech environment we mostly don't translate boolean constants in
computer science.
Regards
Pavel
Null is different - there is not known any formal symbol for null.
Show quoted text
--
Álvaro Herrera Breisgau, Deutschland —
https://www.EnterpriseDB.com/
"On the other flipper, one wrong move and we're Fatal Exceptions"
(T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/)
On 2025-Oct-21, Álvaro Herrera wrote:
On 2025-Oct-20, David G. Johnston wrote:
Thank you. Seems good from a quick read. I’m regretting the choice of the
display_ prefix; is there any technical limitation or other opposition to
using just true and false?\pset true ‘true’
\pset false ‘false’
Uhm. I don't know. [...] I'll gladly take a vote.
I got zero votes and lots of digression, so I have pushed with your
original choice of "display_true" and "display_false". The "true" and
"false" variable names sound too generic and I think they're more likely
to cause confusion. I think "null" is not a great name either, but it's
been there since forever so I'm not going to propose changing it.
It's always been the case that for machine-readable output, --no-psqlrc
should be used, and a majority of interesting scripts I've seen do that
already, so I don't expect lots of breakage. (Also, such a script is
easy to fix if anyone runs into trouble.)
I have added this to my stock .psqlrc as dogfooding experiment:
select :VERSION_NUM >= 190000 as bool_display \gset
\if :bool_display
\pset display_true YES
\pset display_false no
\unset bool_display
\endif
This means I get support for it when connecting to older servers with
new psql, and if I use the old psql, things behave normally with no
extra noise.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
On Monday, November 3, 2025, Álvaro Herrera <alvherre@kurilemu.de> wrote:
On 2025-Oct-21, Álvaro Herrera wrote:
On 2025-Oct-20, David G. Johnston wrote:
Thank you. Seems good from a quick read. I’m regretting the choice
of the
display_ prefix; is there any technical limitation or other opposition
to
using just true and false?
\pset true ‘true’
\pset false ‘false’Uhm. I don't know. [...] I'll gladly take a vote.
I got zero votes and lots of digression, so I have pushed with your
original choice of "display_true" and "display_false". The "true" and
"false" variable names sound too generic and I think they're more likely
to cause confusion. I think "null" is not a great name either, but it's
been there since forever so I'm not going to propose changing it.
Thank you.
David J.