psql: add \pset true/false

On Wed, Oct 28, 2015 at 10:03 AM, Marko Tiikkaja <marko@joh.to> wrote:

Hello hello,

Since the default t/f output for booleans is not very user friendly,
attached is a patch which enables you to do for example the following:

=# \pset true TRUE
Boolean TRUE display is "TRUE".
=# \pset false FALSE
Boolean FALSE display is "FALSE".
=# select true, false;
bool | bool
------+-------
TRUE | FALSE
(1 row)

(For anyone reviewing: I didn't touch the parts of describe.c which do this
for nullPrint:

myopt.nullPrint = NULL;

since I thought it was dubious in the first place, and I don't think we
output booleans in the describe commands.)

-0 on this concept from me. I'm not going to vigorously oppose it, but:

1. You can always do it in the query if you really want it.
2. If you're the sort of person liable to be confused by t/f, you
probably aren't in the target audience for psql anyway.
3. I really don't want to end up with a bunch of features of this type
for a variety of different data types.

But I just work here, and if others feel differently, so be it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Oct 28, 2015 at 10:52 PM, Robert Haas <robertmhaas@gmail.com> wrote:

3. I really don't want to end up with a bunch of features of this type
for a variety of different data types.

We already have \pset null which feels very similar. It's not like 'f'
and 't' are terribly common and probably different from how they
render in whatever driver the user's probably coding to.

On the other hand if their driver isn't mapping booleans to a native
data type and just providing the Postgres text output then then this
is only risking more confusion by presenting the user with a rendering
that's different from what they need to be expecting. IIRC this is the
case for the PHP driver for example.

--
greg

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2015-10-28 23:38:45 +0000, Greg Stark wrote:

On Wed, Oct 28, 2015 at 10:52 PM, Robert Haas <robertmhaas@gmail.com> wrote:

3. I really don't want to end up with a bunch of features of this type
for a variety of different data types.

We already have \pset null which feels very similar.

It's data type neutral, and there's no representation of NULL that's
definitely discernible from the corresponding string. So I don't see
those being the same.

This seems like opening a can of worms to me. Why not add formatting
options in psql for other data types?

- Andres

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 10/28/15 11:52 PM, Robert Haas wrote:

-0 on this concept from me. I'm not going to vigorously oppose it, but:

1. You can always do it in the query if you really want it.

True, but not always practical.

2. If you're the sort of person liable to be confused by t/f, you
probably aren't in the target audience for psql anyway.

Really? The difference between t/f is that the vertical squiggle is
flipped, THAT'S IT. Consider:

t t f f f
f t f t f

Saying that I'm not target audience for not being able to see WTF is
going on above I find offending.

3. I really don't want to end up with a bunch of features of this type
for a variety of different data types.

Fine. Then let's not add it for a different variety of data types. But
boolean is quite essential and it has a really small number of valid
output values.

.m

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Oct 29, 2015 at 1:32 AM, Marko Tiikkaja <marko@joh.to> wrote:

2. If you're the sort of person liable to be confused by t/f, you
probably aren't in the target audience for psql anyway.

Really? The difference between t/f is that the vertical squiggle is
flipped, THAT'S IT. Consider:

t t f f f
f t f t f

Saying that I'm not target audience for not being able to see WTF is going
on above I find offending.

Sorry, no offense intended. It's really just never happened to me
that I've had a problem with this, and I've been using psql for quite
a few years now. I do agree that if you have a bunch of values in a
row it's more apt to be confusing than with just one, but they won't
normally be as closely spaced as you have them there, because psql
inserts spacing and borders and column headers are usually more than
one character.

But I don't really want to argue about this. I respect your opinion,
and I've given you mine, and wherever we end up based on the opinions
of others is OK with me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Marko Tiikkaja wrote:

Since the default t/f output for booleans is not very user friendly,
attached is a patch which enables you to do for example the following:

Personally I think it would be worth having, but how about
booleans inside ROW() or composite types ?

test=> \pset true 1
Boolean TRUE display is "1".
test=> \pset false 0
Boolean FALSE display is "0".

test #1:

test=> select 't'::bool,'f'::bool;
bool | bool
------+------
1 | 0

test #2:

test=> select ('t'::bool,'f'::bool);
row
-------
(t,f)

test #3:

test=> create type abc as (a bool, b bool, c bool);
test=> select (true,false,true)::abc;
row
---------
(t,f,t)

I understand that the patch translates t/f only if the output type
has the OID for bool, which is not the case in #2 or #3, but I guess
users would expect #2 to output (1,0) and #3 (1,0,1).

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 10/29/15 11:51 AM, Daniel Verite wrote:

Marko Tiikkaja wrote:

Since the default t/f output for booleans is not very user friendly,
attached is a patch which enables you to do for example the following:

Personally I think it would be worth having, but how about
booleans inside ROW() or composite types ?

There's not enough information sent over to do that in the client.

Note that this works the same way as \pset null with SELECT
ROW(NULL), so I don't consider it a show stopper for the patch.

.m

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 29 Oct 2015, at 08:50, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Oct 29, 2015 at 1:32 AM, Marko Tiikkaja <marko@joh.to> wrote:

2. If you're the sort of person liable to be confused by t/f, you
probably aren't in the target audience for psql anyway.

Really? The difference between t/f is that the vertical squiggle is
flipped, THAT'S IT. Consider:

t t f f f
f t f t f

Saying that I'm not target audience for not being able to see WTF is going
on above I find offending.

Sorry, no offense intended. It's really just never happened to me
that I've had a problem with this, and I've been using psql for quite
a few years now. I do agree that if you have a bunch of values in a
row it's more apt to be confusing than with just one, but they won't
normally be as closely spaced as you have them there, because psql
inserts spacing and borders and column headers are usually more than
one character.

I have had exactly this situation a week ago. I was testing the output of an algorithm that is supposed to have exactly one true value per input id.

In the first screenshot, psql_true_false_off.png, you see the output of psql unpatched. Notice how there’s barely any obvious difference between ’t’ and ‘f’. It’s hard to verify that the supposed output is correct. There’s also some column with all-false values, I would need to carefully examine all the f and t characters to find that.

In the second screenshot, psql_true_false_on.png, I applied Marko’s patch and set a colorful emoji character as the display string for true and false. Now it’s relatively easy to verify that there’s exactly one true value. In addition, it’s hard to miss that the first column is always false (for this particular range). I wasn’t looking for that, but this presentation made it strikingly obvious. In the first screenshot, it would remain hidden. (Did you notice the two columns there that have all false values?)

I’ve been testing Marko’s patch for a few months now, and I’ve found it helps a lot in recognizing booleans in many contexts.

Marko Tiikkaja <marko@joh.to> writes:

On 10/29/15 11:51 AM, Daniel Verite wrote:

Personally I think it would be worth having, but how about
booleans inside ROW() or composite types ?

There's not enough information sent over to do that in the client.
Note that this works the same way as \pset null with SELECT
ROW(NULL), so I don't consider it a show stopper for the patch.

The problem with that argument is that \pset null is already a kluge
(but at least a datatype-independent one). Now you've added a datatype
specific kluge of the same ilk. It might be useful, it might be short,
but that doesn't make it not a kluge.

The really key argument that hasn't been addressed here is why does such
a behavior belong in psql, rather than elsewhere? Surely legibility
problems aren't unique to psql users. Moreover, there are exactly
parallel facilities for other datatypes on the server side: think
DateStyle or bytea_output. So if you were trying to follow precedent
rather than invent a kluge, you'd have submitted a patch to create a GUC
that changes the output of boolout().

Now, that would create a debate about backwards compatibility and whether
making bool output more readable was worth possibly breaking some
applications, but I do not think this patch should escape scrutiny for the
same issue. There are plenty of people with shell scripts that look at
psql output, which might get broken by careless use of this \pset.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Oct 29, 2015 at 10:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Marko Tiikkaja <marko@joh.to> writes:

On 10/29/15 11:51 AM, Daniel Verite wrote:

Personally I think it would be worth having, but how about
booleans inside ROW() or composite types ?

There's not enough information sent over to do that in the client.
Note that this works the same way as \pset null with SELECT
ROW(NULL), so I don't consider it a show stopper for the patch.

The problem with that argument is that \pset null is already a kluge
(but at least a datatype-independent one). Now you've added a datatype
specific kluge of the same ilk. It might be useful, it might be short,
but that doesn't make it not a kluge.

FWIW, I am -1 on the concept of enforcing output values for particular
datatypes because that's not the job of psql, and it is easy to do
that at query level (no comment about the existing \pset NULL stuff).

+    fprintf(output, _("  true               set the string to be
prined in place of a TRUE value\n"));
+    fprintf(output, _("  false              set the string to be
prined in place of a FALSE value\n"));
s/prined/printed/
-- 
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 11/12/15 1:50 PM, Michael Paquier wrote:

FWIW, I am -1 on the concept of enforcing output values for particular
datatypes because that's not the job of psql

In my view, the job of psql is to make working with a postgres database
easy for us human beings. That means (among other things) formatting
and aligning query output for readability. I don't see how reformatting
unreadable boolean values meant for computers is that big of a stretch.

.m

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, 30 Oct 2015 at 00:51 Tom Lane <tgl@sss.pgh.pa.us> wrote:

The really key argument that hasn't been addressed here is why does such
a behavior belong in psql, rather than elsewhere? Surely legibility
problems aren't unique to psql users. Moreover, there are exactly
parallel facilities for other datatypes on the server side: think
DateStyle or bytea_output. So if you were trying to follow precedent
rather than invent a kluge, you'd have submitted a patch to create a GUC
that changes the output of boolout().

I find Tom's analogy to datestyle and bytea_output convincing.

+1 for a GUC that changes the behaviour of boolout.

And also +1 for doing anything at all to improve on the t/f output. Those
glyphs are way too similar to each other.

I think U+2713 and U+2717 (✓ and ✗) are the obvious choices for a boolean,
but if we have a GUC we can set this to taste.

Cheers,
BJ

On 10/28/2015 10:03 AM, Marko Tiikkaja wrote:

Hello hello,

Since the default t/f output for booleans is not very user friendly,
attached is a patch which enables you to do for example the following:

=# \pset true TRUE
Boolean TRUE display is "TRUE".
=# \pset false FALSE
Boolean FALSE display is "FALSE".
=# select true, false;
bool | bool
------+-------
TRUE | FALSE
(1 row)

I think the battle is already lost, but I'm casting my vote in favor of
this patch anyway.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12 Nov 2015, at 14:21, Brendan Jurd <direvus@gmail.com> wrote:

On Fri, 30 Oct 2015 at 00:51 Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:
The really key argument that hasn't been addressed here is why does such
a behavior belong in psql, rather than elsewhere? Surely legibility
problems aren't unique to psql users. Moreover, there are exactly
parallel facilities for other datatypes on the server side: think
DateStyle or bytea_output. So if you were trying to follow precedent
rather than invent a kluge, you'd have submitted a patch to create a GUC
that changes the output of boolout().

I find Tom's analogy to datestyle and bytea_output convincing.

+1 for a GUC that changes the behaviour of boolout.

-1 for changing boolout(). It will break anything that receives boolean values from the server. How a client is going to display values (of any type) is logic that should belong in the client, not in the protocol.

On 11/12/2015 05:41 PM, Matthijs van der Vleuten wrote:

On 12 Nov 2015, at 14:21, Brendan Jurd <direvus@gmail.com> wrote:

On Fri, 30 Oct 2015 at 00:51 Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:
The really key argument that hasn't been addressed here is why does such
a behavior belong in psql, rather than elsewhere? Surely legibility
problems aren't unique to psql users. Moreover, there are exactly
parallel facilities for other datatypes on the server side: think
DateStyle or bytea_output. So if you were trying to follow precedent
rather than invent a kluge, you'd have submitted a patch to create a GUC
that changes the output of boolout().

I find Tom's analogy to datestyle and bytea_output convincing.

+1 for a GUC that changes the behaviour of boolout.

-1 for changing boolout(). It will break anything that receives boolean values from the server. How a client is going to display values (of any type) is logic that should belong in the client, not in the protocol.

I fully agree. This is something I feel should happen in the client.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-11-12 17:41 GMT+01:00 Matthijs van der Vleuten <matthijs@zr40.nl>:

On 12 Nov 2015, at 14:21, Brendan Jurd <direvus@gmail.com> wrote:

On Fri, 30 Oct 2015 at 00:51 Tom Lane <tgl@sss.pgh.pa.us> wrote:

The really key argument that hasn't been addressed here is why does such
a behavior belong in psql, rather than elsewhere? Surely legibility
problems aren't unique to psql users. Moreover, there are exactly
parallel facilities for other datatypes on the server side: think
DateStyle or bytea_output. So if you were trying to follow precedent
rather than invent a kluge, you'd have submitted a patch to create a GUC
that changes the output of boolout().

I find Tom's analogy to datestyle and bytea_output convincing.

+1 for a GUC that changes the behaviour of boolout.

-1 for changing boolout(). It will break anything that receives boolean
values from the server. How a client is going to display values (of any
type) is logic that should belong in the client, not in the protocol.

This is not fully valid, because transformation from binary to text is
processed on server side. And client side transformation is easy only for
scalar values.

Regards

Pavel

Matthijs van der Vleuten wrote:

-1 for changing boolout(). It will break anything that receives
boolean values from the server.

Not if the default output is still 't' or 'f' like now.
Nobody seems to suggest a gratuitous compatibility break.

How a client is going to display values (of any
type) is logic that should belong in the client, not in the protocol.

Maybe in general, but consider two problems here:

#1: postgres type extensibility implies that clients
display values from types they know nothing about.
It makes sense that they just use the text representation
that comes from the server, unless they have a specific
reason not to.

#2: in the case of built-in types, like boolean, it's not sufficient
to change how the base type gets displayed.
With Marko's patch, array[true,false] is still displayed as {t,f}
when setting aternatives through the proposed \pset
feature. Same problem inside composite types as mentioned
upthread.

The issue with the patch's approach is that it falls short of
identifying bools in all situations, so the results are inconsistent.
Solving that in psql is hard or possibly irrealistic, because
the reliance on the text representation for complex types goes deep.

By contrast, making the server-side representation configurable seems
easier. Plus it might be of interest for other consumers of resultsets,
outside of psql.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Oct 29, 2015 at 5:28 AM, Matthijs van der Vleuten <matthijs@zr40.nl>
wrote:

I have had exactly this situation a week ago. I was testing the output of
an algorithm that is supposed to have exactly one true value per input id.

​If this is particularly important I would add something like (and(col1,
col2, col3, ...) = true) and/or NULLIF((not(col1)::int + not(col2)::int
..., 0) to the grid and check/test those instead of visually scanning the
output.

​If the pretty presentation is destined for final output I'd say you really
want to output text and write a function so that the logic is embedded in
the query and not a side-effect of a specific environment.

David J.​

On Thu, Oct 29, 2015 at 6:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Marko Tiikkaja <marko@joh.to> writes:

On 10/29/15 11:51 AM, Daniel Verite wrote:

Personally I think it would be worth having, but how about
booleans inside ROW() or composite types ?

There's not enough information sent over to do that in the client.
Note that this works the same way as \pset null with SELECT
ROW(NULL), so I don't consider it a show stopper for the patch.

The problem with that argument is that \pset null is already a kluge
(but at least a datatype-independent one). Now you've added a datatype
specific kluge of the same ilk. It might be useful, it might be short,
but that doesn't make it not a kluge.

The really key argument that hasn't been addressed here is why does such
a behavior belong in psql, rather than elsewhere? Surely legibility
problems aren't unique to psql users. Moreover, there are exactly
parallel facilities for other datatypes on the server side: think
DateStyle

​Which provides a finite set of possible values.
​

or bytea_output.

​Wasn't this added mostly for performance as opposed to dealing with
"locale/style" considerations?​

So if you were trying to follow precedent

rather than invent a kluge, you'd have submitted a patch to create a GUC
that changes the output of boolout().

​I'm leaning toward doing this in the client if its offered at all. An
unobtrusive usability enhancement - even if limited to non-embedded
situations - that seems like little effort for a measurable gain.

​David J.