Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Add \pset options for boolean value display
- 645cb44c5490 19 (unreleased) landed

Add \pset options for boolean value display

David G. Johnston <david.g.johnston@gmail.com> — 2025-03-21T03:24:46Z

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.

Re: Add \pset options for boolean value display

David G. Johnston <david.g.johnston@gmail.com> — 2025-06-24T22:18:31Z

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.

Re: Add \pset options for boolean value display

Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-24T22:30:58Z

"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

Re: Add \pset options for boolean value display

David G. Johnston <david.g.johnston@gmail.com> — 2025-06-24T22:43:02Z

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.

Re: Add \pset options for boolean value display

Daniel Verite <daniel@manitou-mail.org> — 2025-06-25T18:03:01Z

	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]) where pretty much
the same idea was proposed (and eventually rejected at the time).

[1] https://postgr.es/m/56308F56.8060908%40joh.to

Best regards,
-- 
Daniel Vérité 
https://postgresql.verite.pro/

Re: Add \pset options for boolean value display

David G. Johnston <david.g.johnston@gmail.com> — 2025-06-25T18:21:56Z

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).
>
>
> [1] https://postgr.es/m/56308F56.8060908%40joh.to
>
>
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.

Re: Add \pset options for boolean value display

Vik Fearing <vik@postgresfriends.org> — 2025-07-05T12:41:27Z

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

Re: Add \pset options for boolean value display

Álvaro Herrera <alvherre@kurilemu.de> — 2025-10-20T14:24:36Z

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/

Re: Add \pset options for boolean value display

David G. Johnston <david.g.johnston@gmail.com> — 2025-10-20T20:51:37Z

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.

Re: Add \pset options for boolean value display

Álvaro Herrera <alvherre@kurilemu.de> — 2025-10-20T21:08:21Z

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

Re: Add \pset options for boolean value display

Chao Li <li.evan.chao@gmail.com> — 2025-10-21T01:38:24Z

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

Re: Add \pset options for boolean value display

David G. Johnston <david.g.johnston@gmail.com> — 2025-10-21T02:29:11Z

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.

Re: Add \pset options for boolean value display

David G. Johnston <david.g.johnston@gmail.com> — 2025-10-21T02:48:04Z

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.

Re: Add \pset options for boolean value display

Chao Li <li.evan.chao@gmail.com> — 2025-10-21T02:51:28Z

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

Re: Add \pset options for boolean value display

Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-21T03:37:12Z

"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

Re: Add \pset options for boolean value display

Pavel Stehule <pavel.stehule@gmail.com> — 2025-10-21T11:26:50Z

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


> --
> Á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/)
>
>
>

Re: Add \pset options for boolean value display

Álvaro Herrera <alvherre@kurilemu.de> — 2025-11-03T16:44:23Z

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/

Re: Add \pset options for boolean value display

David G. Johnston <david.g.johnston@gmail.com> — 2025-11-03T17:16:30Z

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.

Re: Add \pset options for boolean value display

Bruce Momjian <bruce@momjian.us> — 2026-05-12T22:28:54Z

On Tue, Apr 21, 2026 at 10:43:02AM -0700, David G. Johnston wrote:
> On Mon, Apr 20, 2026 at 11:30 PM David G. Johnston <david.g.johnston@gmail.com>
> Had issues getting a server to run meson+asan but did get psql to do so and
> confirmed.
> 
> I just didn't find all of the patterns.
> 
> savePsetInfo and restorePsetInfo need explicit knowledge of these options as
> well to clean up the popt struct.
> 
> Attached.

Patch applied.  Let me know if you need any other fixes.  Thanks.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.