PATCH: psql boolean display

Hello all,

I am providing a patch to allow you to change the output of a boolean
value in psql much like you can do with NULL. A client requested this
feature and we thought it may appeal to someone else in the community.

The patch includes updated docs and a regression test. The code
changes themselves are pretty simple and straightforward.

Example from the regression test:

SELECT true, false;
bool | bool
------+------
t | f
(1 row)

\pset booltrue 'foo'
\pset boolfalse 'bar'
SELECT true, false;
bool | bool
------+------
foo | bar
(1 row)

\pset booltrue 't'
\pset boolfalse 'f'
SELECT true, false;
bool | bool
------+------
t | f
(1 row)

As always, comments welcome.

Thanks.

On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com> wrote:
> I am providing a patch to allow you to change the output of a boolean
> value in psql much like you can do with NULL. A client requested this
> feature and we thought it may appeal to someone else in the community.
>
> The patch includes updated docs and a regression test. The code
> changes themselves are pretty simple and straightforward.
>
> Example from the regression test:
>
> SELECT true, false;
> bool | bool
> ------+------
> t | f
> (1 row)
>
> \pset booltrue 'foo'
> \pset boolfalse 'bar'
> SELECT true, false;
> bool | bool
> ------+------
> foo | bar
> (1 row)
>
> \pset booltrue 't'
> \pset boolfalse 'f'
> SELECT true, false;
> bool | bool
> ------+------
> t | f
> (1 row)
>
> As always, comments welcome.

Why not just do it in the SQL?

SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;

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

2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com> wrote:
>> I am providing a patch to allow you to change the output of a boolean
>> value in psql much like you can do with NULL. A client requested this
>> feature and we thought it may appeal to someone else in the community.
>>
>> The patch includes updated docs and a regression test. The code
>> changes themselves are pretty simple and straightforward.
>>
>> Example from the regression test:
>>
>> SELECT true, false;
>> bool | bool
>> ------+------
>> t | f
>> (1 row)
>>
>> \pset booltrue 'foo'
>> \pset boolfalse 'bar'
>> SELECT true, false;
>> bool | bool
>> ------+------
>> foo | bar
>> (1 row)
>>
>> \pset booltrue 't'
>> \pset boolfalse 'f'
>> SELECT true, false;
>> bool | bool
>> ------+------
>> t | f
>> (1 row)
>>
>> As always, comments welcome.
>
> Why not just do it in the SQL?
>
> SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;

I understand this motivation - although I was more happy with server
side solution.

Regards

Pavel Stehule

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
> 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
> > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com> wrote:
> >> I am providing a patch to allow you to change the output of a boolean
> >> value in psql much like you can do with NULL. A client requested this
> >> feature and we thought it may appeal to someone else in the community.
> >>
> >> The patch includes updated docs and a regression test. The code
> >> changes themselves are pretty simple and straightforward.
> >>
> >> Example from the regression test:
> >>
> >> SELECT true, false;
> >> bool | bool
> >> ------+------
> >> t | f
> >> (1 row)
> >>
> >> \pset booltrue 'foo'
> >> \pset boolfalse 'bar'
> >> SELECT true, false;
> >> bool | bool
> >> ------+------
> >> foo | bar
> >> (1 row)
> >>
> >> \pset booltrue 't'
> >> \pset boolfalse 'f'
> >> SELECT true, false;
> >> bool | bool
> >> ------+------
> >> t | f
> >> (1 row)
> >>
> >> As always, comments welcome.
> >
> > Why not just do it in the SQL?
> >
> > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
>
> I understand this motivation - although I was more happy with server
> side solution.
>

Was a server side implementation submitted before? I can change it, but I
did it on the client side like the null display was done.

> Regards
>
> Pavel Stehule
>
> >
> > --
> > Robert Haas
> > EnterpriseDB: http://www.enterprisedb.com
> > The Enterprise PostgreSQL Company
> >
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers

On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil(at)omniti(dot)com> wrote:
>
> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:
> >
> > 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com> wrote:
> > >> I am providing a patch to allow you to change the output of a boolean
> > >> value in psql much like you can do with NULL. A client requested this
> > >> feature and we thought it may appeal to someone else in the
community.
> > >>
> > >> The patch includes updated docs and a regression test. The code
> > >> changes themselves are pretty simple and straightforward.
> > >>
> > >> Example from the regression test:
> > >>
> > >> SELECT true, false;
> > >> bool | bool
> > >> ------+------
> > >> t | f
> > >> (1 row)
> > >>
> > >> \pset booltrue 'foo'
> > >> \pset boolfalse 'bar'
> > >> SELECT true, false;
> > >> bool | bool
> > >> ------+------
> > >> foo | bar
> > >> (1 row)
> > >>
> > >> \pset booltrue 't'
> > >> \pset boolfalse 'f'
> > >> SELECT true, false;
> > >> bool | bool
> > >> ------+------
> > >> t | f
> > >> (1 row)
> > >>
> > >> As always, comments welcome.
> > >
> > > Why not just do it in the SQL?
> > >
> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
> >
> > I understand this motivation - although I was more happy with server
> > side solution.
> >
>
> Was a server side implementation submitted before? I can change it, but I
did it on the client side like the null display was done.

Or how about both?

>
> > Regards
> >
> > Pavel Stehule
> >
> > >
> > > --
> > > Robert Haas
> > > EnterpriseDB: http://www.enterprisedb.com
> > > The Enterprise PostgreSQL Company
> > >
> > >
> > > --
> > > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-hackers

On 20 August 2012 22:10, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com> wrote:
>>> I am providing a patch to allow you to change the output of a boolean
>>> value in psql much like you can do with NULL. A client requested this
>>> feature and we thought it may appeal to someone else in the community.
>>>
>>> The patch includes updated docs and a regression test. The code
>>> changes themselves are pretty simple and straightforward.
>>>
>>> Example from the regression test:
>>>
>>> SELECT true, false;
>>> bool | bool
>>> ------+------
>>> t | f
>>> (1 row)
>>>
>>> \pset booltrue 'foo'
>>> \pset boolfalse 'bar'
>>> SELECT true, false;
>>> bool | bool
>>> ------+------
>>> foo | bar
>>> (1 row)
>>>
>>> \pset booltrue 't'
>>> \pset boolfalse 'f'
>>> SELECT true, false;
>>> bool | bool
>>> ------+------
>>> t | f
>>> (1 row)
>>>
>>> As always, comments welcome.
>>
>> Why not just do it in the SQL?
>>
>> SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
>
> I understand this motivation - although I was more happy with server
> side solution.

Wouldn't a server-side solution risk breaking many things that depends
on such a representation? You wouldn't be able to pick and choose
what comes out of the server on a per-application basis unless you use
cumbersome CASE clauses in every query that returns boolean data.

It sounds like keeping it at the application level is the least
disruptive, and there is a precedent for such changes, such as NULL
representation.

--
Thom

On 20 August 2012 22:31, Phil Sorber <phil(at)omniti(dot)com> wrote:
> On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil(at)omniti(dot)com> wrote:
>>
>> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> >
>> > 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com> wrote:
>> > >> I am providing a patch to allow you to change the output of a boolean
>> > >> value in psql much like you can do with NULL. A client requested this
>> > >> feature and we thought it may appeal to someone else in the
>> > >> community.
>> > >>
>> > >> The patch includes updated docs and a regression test. The code
>> > >> changes themselves are pretty simple and straightforward.
>> > >>
>> > >> Example from the regression test:
>> > >>
>> > >> SELECT true, false;
>> > >> bool | bool
>> > >> ------+------
>> > >> t | f
>> > >> (1 row)
>> > >>
>> > >> \pset booltrue 'foo'
>> > >> \pset boolfalse 'bar'
>> > >> SELECT true, false;
>> > >> bool | bool
>> > >> ------+------
>> > >> foo | bar
>> > >> (1 row)
>> > >>
>> > >> \pset booltrue 't'
>> > >> \pset boolfalse 'f'
>> > >> SELECT true, false;
>> > >> bool | bool
>> > >> ------+------
>> > >> t | f
>> > >> (1 row)
>> > >>
>> > >> As always, comments welcome.
>> > >
>> > > Why not just do it in the SQL?
>> > >
>> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
>> >
>> > I understand this motivation - although I was more happy with server
>> > side solution.
>> >
>>
>> Was a server side implementation submitted before? I can change it, but I
>> did it on the client side like the null display was done.
>
> Or how about both?

Surely one would break the other?

--
Thom

2012/8/20 Thom Brown <thom(at)linux(dot)com>:
> On 20 August 2012 22:10, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
>>> On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com> wrote:
>>>> I am providing a patch to allow you to change the output of a boolean
>>>> value in psql much like you can do with NULL. A client requested this
>>>> feature and we thought it may appeal to someone else in the community.
>>>>
>>>> The patch includes updated docs and a regression test. The code
>>>> changes themselves are pretty simple and straightforward.
>>>>
>>>> Example from the regression test:
>>>>
>>>> SELECT true, false;
>>>> bool | bool
>>>> ------+------
>>>> t | f
>>>> (1 row)
>>>>
>>>> \pset booltrue 'foo'
>>>> \pset boolfalse 'bar'
>>>> SELECT true, false;
>>>> bool | bool
>>>> ------+------
>>>> foo | bar
>>>> (1 row)
>>>>
>>>> \pset booltrue 't'
>>>> \pset boolfalse 'f'
>>>> SELECT true, false;
>>>> bool | bool
>>>> ------+------
>>>> t | f
>>>> (1 row)
>>>>
>>>> As always, comments welcome.
>>>
>>> Why not just do it in the SQL?
>>>
>>> SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
>>
>> I understand this motivation - although I was more happy with server
>> side solution.
>
> Wouldn't a server-side solution risk breaking many things that depends
> on such a representation? You wouldn't be able to pick and choose
> what comes out of the server on a per-application basis unless you use
> cumbersome CASE clauses in every query that returns boolean data.
>
> It sounds like keeping it at the application level is the least
> disruptive, and there is a precedent for such changes, such as NULL
> representation.

It is similar to datetime formatting or to number formatting.

Sometimes it is issue for some untyped languages because 't' or 'f'
has not adequate sense - but it is unfriendly when somebody working
with console. console based solution like proposed patch is probably
the most less evil solution. Using 't' and 'f' was not a best idea.

>
> --
> Thom

On Aug 20, 2012 5:56 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
>
> On 20 August 2012 22:31, Phil Sorber <phil(at)omniti(dot)com> wrote:
> > On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil(at)omniti(dot)com> wrote:
> >>
> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
wrote:
> >> >
> >> > 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
> >> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com>
wrote:
> >> > >> I am providing a patch to allow you to change the output of a
boolean
> >> > >> value in psql much like you can do with NULL. A client requested
this
> >> > >> feature and we thought it may appeal to someone else in the
> >> > >> community.
> >> > >>
> >> > >> The patch includes updated docs and a regression test. The code
> >> > >> changes themselves are pretty simple and straightforward.
> >> > >>
> >> > >> Example from the regression test:
> >> > >>
> >> > >> SELECT true, false;
> >> > >> bool | bool
> >> > >> ------+------
> >> > >> t | f
> >> > >> (1 row)
> >> > >>
> >> > >> \pset booltrue 'foo'
> >> > >> \pset boolfalse 'bar'
> >> > >> SELECT true, false;
> >> > >> bool | bool
> >> > >> ------+------
> >> > >> foo | bar
> >> > >> (1 row)
> >> > >>
> >> > >> \pset booltrue 't'
> >> > >> \pset boolfalse 'f'
> >> > >> SELECT true, false;
> >> > >> bool | bool
> >> > >> ------+------
> >> > >> t | f
> >> > >> (1 row)
> >> > >>
> >> > >> As always, comments welcome.
> >> > >
> >> > > Why not just do it in the SQL?
> >> > >
> >> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
> >> >
> >> > I understand this motivation - although I was more happy with server
> >> > side solution.
> >> >
> >>
> >> Was a server side implementation submitted before? I can change it,
but I
> >> did it on the client side like the null display was done.
> >
> > Or how about both?
>
> Surely one would break the other?
>

If using both.

> --
> Thom

On 20 August 2012 23:06, Phil Sorber <phil(at)omniti(dot)com> wrote:
>
> On Aug 20, 2012 5:56 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
>>
>> On 20 August 2012 22:31, Phil Sorber <phil(at)omniti(dot)com> wrote:
>> > On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil(at)omniti(dot)com> wrote:
>> >>
>> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
>> >> wrote:
>> >> >
>> >> > 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> >> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com>
>> >> > > wrote:
>> >> > >> I am providing a patch to allow you to change the output of a
>> >> > >> boolean
>> >> > >> value in psql much like you can do with NULL. A client requested
>> >> > >> this
>> >> > >> feature and we thought it may appeal to someone else in the
>> >> > >> community.
>> >> > >>
>> >> > >> The patch includes updated docs and a regression test. The code
>> >> > >> changes themselves are pretty simple and straightforward.
>> >> > >>
>> >> > >> Example from the regression test:
>> >> > >>
>> >> > >> SELECT true, false;
>> >> > >> bool | bool
>> >> > >> ------+------
>> >> > >> t | f
>> >> > >> (1 row)
>> >> > >>
>> >> > >> \pset booltrue 'foo'
>> >> > >> \pset boolfalse 'bar'
>> >> > >> SELECT true, false;
>> >> > >> bool | bool
>> >> > >> ------+------
>> >> > >> foo | bar
>> >> > >> (1 row)
>> >> > >>
>> >> > >> \pset booltrue 't'
>> >> > >> \pset boolfalse 'f'
>> >> > >> SELECT true, false;
>> >> > >> bool | bool
>> >> > >> ------+------
>> >> > >> t | f
>> >> > >> (1 row)
>> >> > >>
>> >> > >> As always, comments welcome.
>> >> > >
>> >> > > Why not just do it in the SQL?
>> >> > >
>> >> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
>> >> >
>> >> > I understand this motivation - although I was more happy with server
>> >> > side solution.
>> >> >
>> >>
>> >> Was a server side implementation submitted before? I can change it, but
>> >> I
>> >> did it on the client side like the null display was done.
>> >
>> > Or how about both?
>>
>> Surely one would break the other?
>>
>
> If using both.

Yes. :)

--
Thom

On Mon, Aug 20, 2012 at 5:54 PM, Thom Brown <thom(at)linux(dot)com> wrote:

> On 20 August 2012 22:10, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
> >> On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com> wrote:
> >>> I am providing a patch to allow you to change the output of a boolean
> >>> value in psql much like you can do with NULL. A client requested this
> >>> feature and we thought it may appeal to someone else in the community.
> >>>
> >>> The patch includes updated docs and a regression test. The code
> >>> changes themselves are pretty simple and straightforward.
> >>>
> >>> Example from the regression test:
> >>>
> >>> SELECT true, false;
> >>> bool | bool
> >>> ------+------
> >>> t | f
> >>> (1 row)
> >>>
> >>> \pset booltrue 'foo'
> >>> \pset boolfalse 'bar'
> >>> SELECT true, false;
> >>> bool | bool
> >>> ------+------
> >>> foo | bar
> >>> (1 row)
> >>>
> >>> \pset booltrue 't'
> >>> \pset boolfalse 'f'
> >>> SELECT true, false;
> >>> bool | bool
> >>> ------+------
> >>> t | f
> >>> (1 row)
> >>>
> >>> As always, comments welcome.
> >>
> >> Why not just do it in the SQL?
> >>
> >> SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
> >
> > I understand this motivation - although I was more happy with server
> > side solution.
>
> Wouldn't a server-side solution risk breaking many things that depends
> on such a representation? You wouldn't be able to pick and choose
> what comes out of the server on a per-application basis unless you use
> cumbersome CASE clauses in every query that returns boolean data.
>
> It sounds like keeping it at the application level is the least
> disruptive, and there is a precedent for such changes, such as NULL
> representation.

On occasions I have wanted psql to emit the full 'True'/'False' words
instead of cryptic one-letter t/f, which can get lost on long rows that get
wrapped around on screen. Writing long-winded CASE expressions to get the
effect is too much for small ad-hoc queries.

I thought of inventing a data type whose out-function would emit these
strings, and tack a ::mybool to the expression I want modified. But that
would break the applications if somebody pasted the same query in an
application (JDBC or some such that understands boolean) and expected a
boolean data type instead of a text output of an expression.

I think there's a merit to psql supporting this feature, because psql is
most commonly used for ad-hoc interactive use, and true/false is more human
consumable than t/f (I have had a Java developer ask me what was that 't'
value in the resultset in psql).

--
Gurjeet Singh

On Aug 20, 2012 6:08 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
>
> On 20 August 2012 23:06, Phil Sorber <phil(at)omniti(dot)com> wrote:
> >
> > On Aug 20, 2012 5:56 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
> >>
> >> On 20 August 2012 22:31, Phil Sorber <phil(at)omniti(dot)com> wrote:
> >> > On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil(at)omniti(dot)com> wrote:
> >> >>
> >> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
> >> >> wrote:
> >> >> >
> >> >> > 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
> >> >> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com>
> >> >> > > wrote:
> >> >> > >> I am providing a patch to allow you to change the output of a
> >> >> > >> boolean
> >> >> > >> value in psql much like you can do with NULL. A client
requested
> >> >> > >> this
> >> >> > >> feature and we thought it may appeal to someone else in the
> >> >> > >> community.
> >> >> > >>
> >> >> > >> The patch includes updated docs and a regression test. The code
> >> >> > >> changes themselves are pretty simple and straightforward.
> >> >> > >>
> >> >> > >> Example from the regression test:
> >> >> > >>
> >> >> > >> SELECT true, false;
> >> >> > >> bool | bool
> >> >> > >> ------+------
> >> >> > >> t | f
> >> >> > >> (1 row)
> >> >> > >>
> >> >> > >> \pset booltrue 'foo'
> >> >> > >> \pset boolfalse 'bar'
> >> >> > >> SELECT true, false;
> >> >> > >> bool | bool
> >> >> > >> ------+------
> >> >> > >> foo | bar
> >> >> > >> (1 row)
> >> >> > >>
> >> >> > >> \pset booltrue 't'
> >> >> > >> \pset boolfalse 'f'
> >> >> > >> SELECT true, false;
> >> >> > >> bool | bool
> >> >> > >> ------+------
> >> >> > >> t | f
> >> >> > >> (1 row)
> >> >> > >>
> >> >> > >> As always, comments welcome.
> >> >> > >
> >> >> > > Why not just do it in the SQL?
> >> >> > >
> >> >> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
> >> >> >
> >> >> > I understand this motivation - although I was more happy with
server
> >> >> > side solution.
> >> >> >
> >> >>
> >> >> Was a server side implementation submitted before? I can change it,
but
> >> >> I
> >> >> did it on the client side like the null display was done.
> >> >
> >> > Or how about both?
> >>
> >> Surely one would break the other?
> >>
> >
> > If using both.
>
> Yes. :)

Really server would override client.

>
> --
> Thom

Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:

> On occasions I have wanted psql to emit the full 'True'/'False'
> words instead of cryptic one-letter t/f, which can get lost on
> long rows that get wrapped around on screen. Writing long-winded
> CASE expressions to get the effect is too much for small ad-hoc
> queries.
>
> I thought of inventing a data type whose out-function would emit
> these strings, and tack a ::mybool to the expression I want
> modified. But that would break the applications if somebody pasted
> the same query in an application (JDBC or some such that
> understands boolean) and expected a boolean data type instead of a
> text output of an expression.

The type itself does output true/false; it's just psql that uses
t/f.

test=# select 'true'::boolean::text;
text
------
true
(1 row)

test=# select 'false'::boolean::text;
text
-------
false
(1 row)

That has always seemed quite odd (and occasionally inconvenient) to
me.

-Kevin

On 20 August 2012 23:16, Phil Sorber <phil(at)omniti(dot)com> wrote:
>
> On Aug 20, 2012 6:08 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
>>
>> On 20 August 2012 23:06, Phil Sorber <phil(at)omniti(dot)com> wrote:
>> >
>> > On Aug 20, 2012 5:56 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
>> >>
>> >> On 20 August 2012 22:31, Phil Sorber <phil(at)omniti(dot)com> wrote:
>> >> > On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil(at)omniti(dot)com> wrote:
>> >> >>
>> >> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
>> >> >> wrote:
>> >> >> >
>> >> >> > 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> >> >> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)com>
>> >> >> > > wrote:
>> >> >> > >> I am providing a patch to allow you to change the output of a
>> >> >> > >> boolean
>> >> >> > >> value in psql much like you can do with NULL. A client
>> >> >> > >> requested
>> >> >> > >> this
>> >> >> > >> feature and we thought it may appeal to someone else in the
>> >> >> > >> community.
>> >> >> > >>
>> >> >> > >> The patch includes updated docs and a regression test. The code
>> >> >> > >> changes themselves are pretty simple and straightforward.
>> >> >> > >>
>> >> >> > >> Example from the regression test:
>> >> >> > >>
>> >> >> > >> SELECT true, false;
>> >> >> > >> bool | bool
>> >> >> > >> ------+------
>> >> >> > >> t | f
>> >> >> > >> (1 row)
>> >> >> > >>
>> >> >> > >> \pset booltrue 'foo'
>> >> >> > >> \pset boolfalse 'bar'
>> >> >> > >> SELECT true, false;
>> >> >> > >> bool | bool
>> >> >> > >> ------+------
>> >> >> > >> foo | bar
>> >> >> > >> (1 row)
>> >> >> > >>
>> >> >> > >> \pset booltrue 't'
>> >> >> > >> \pset boolfalse 'f'
>> >> >> > >> SELECT true, false;
>> >> >> > >> bool | bool
>> >> >> > >> ------+------
>> >> >> > >> t | f
>> >> >> > >> (1 row)
>> >> >> > >>
>> >> >> > >> As always, comments welcome.
>> >> >> > >
>> >> >> > > Why not just do it in the SQL?
>> >> >> > >
>> >> >> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
>> >> >> >
>> >> >> > I understand this motivation - although I was more happy with
>> >> >> > server
>> >> >> > side solution.
>> >> >> >
>> >> >>
>> >> >> Was a server side implementation submitted before? I can change it,
>> >> >> but
>> >> >> I
>> >> >> did it on the client side like the null display was done.
>> >> >
>> >> > Or how about both?
>> >>
>> >> Surely one would break the other?
>> >>
>> >
>> > If using both.
>>
>> Yes. :)
>
> Really server would override client.

Come to think of it, if the client could detect the server's bool out
config, it could override the server in that instance since it would
know what it was looking for, so perhaps they could coexist.

--
Thom

On Aug 20, 2012 6:28 PM, "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
wrote:
>
> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
>
> > On occasions I have wanted psql to emit the full 'True'/'False'
> > words instead of cryptic one-letter t/f, which can get lost on
> > long rows that get wrapped around on screen. Writing long-winded
> > CASE expressions to get the effect is too much for small ad-hoc
> > queries.
> >
> > I thought of inventing a data type whose out-function would emit
> > these strings, and tack a ::mybool to the expression I want
> > modified. But that would break the applications if somebody pasted
> > the same query in an application (JDBC or some such that
> > understands boolean) and expected a boolean data type instead of a
> > text output of an expression.
>
> The type itself does output true/false; it's just psql that uses
> t/f.
>
> test=# select 'true'::boolean::text;
> text
> ------
> true
> (1 row)
>
> test=# select 'false'::boolean::text;
> text
> -------
> false
> (1 row)
>
> That has always seemed quite odd (and occasionally inconvenient) to
> me.

I think that may be from the cast. I didn't see any transformation in psql.
Looked like it was raw output from the server.

>
> -Kevin

On Aug 20, 2012 6:31 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
>
> On 20 August 2012 23:16, Phil Sorber <phil(at)omniti(dot)com> wrote:
> >
> > On Aug 20, 2012 6:08 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
> >>
> >> On 20 August 2012 23:06, Phil Sorber <phil(at)omniti(dot)com> wrote:
> >> >
> >> > On Aug 20, 2012 5:56 PM, "Thom Brown" <thom(at)linux(dot)com> wrote:
> >> >>
> >> >> On 20 August 2012 22:31, Phil Sorber <phil(at)omniti(dot)com> wrote:
> >> >> > On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil(at)omniti(dot)com> wrote:
> >> >> >>
> >> >> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com
>
> >> >> >> wrote:
> >> >> >> >
> >> >> >> > 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
> >> >> >> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <
phil(at)omniti(dot)com>
> >> >> >> > > wrote:
> >> >> >> > >> I am providing a patch to allow you to change the output of
a
> >> >> >> > >> boolean
> >> >> >> > >> value in psql much like you can do with NULL. A client
> >> >> >> > >> requested
> >> >> >> > >> this
> >> >> >> > >> feature and we thought it may appeal to someone else in the
> >> >> >> > >> community.
> >> >> >> > >>
> >> >> >> > >> The patch includes updated docs and a regression test. The
code
> >> >> >> > >> changes themselves are pretty simple and straightforward.
> >> >> >> > >>
> >> >> >> > >> Example from the regression test:
> >> >> >> > >>
> >> >> >> > >> SELECT true, false;
> >> >> >> > >> bool | bool
> >> >> >> > >> ------+------
> >> >> >> > >> t | f
> >> >> >> > >> (1 row)
> >> >> >> > >>
> >> >> >> > >> \pset booltrue 'foo'
> >> >> >> > >> \pset boolfalse 'bar'
> >> >> >> > >> SELECT true, false;
> >> >> >> > >> bool | bool
> >> >> >> > >> ------+------
> >> >> >> > >> foo | bar
> >> >> >> > >> (1 row)
> >> >> >> > >>
> >> >> >> > >> \pset booltrue 't'
> >> >> >> > >> \pset boolfalse 'f'
> >> >> >> > >> SELECT true, false;
> >> >> >> > >> bool | bool
> >> >> >> > >> ------+------
> >> >> >> > >> t | f
> >> >> >> > >> (1 row)
> >> >> >> > >>
> >> >> >> > >> As always, comments welcome.
> >> >> >> > >
> >> >> >> > > Why not just do it in the SQL?
> >> >> >> > >
> >> >> >> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS
whatever;
> >> >> >> >
> >> >> >> > I understand this motivation - although I was more happy with
> >> >> >> > server
> >> >> >> > side solution.
> >> >> >> >
> >> >> >>
> >> >> >> Was a server side implementation submitted before? I can change
it,
> >> >> >> but
> >> >> >> I
> >> >> >> did it on the client side like the null display was done.
> >> >> >
> >> >> > Or how about both?
> >> >>
> >> >> Surely one would break the other?
> >> >>
> >> >
> >> > If using both.
> >>
> >> Yes. :)
> >
> > Really server would override client.
>
> Come to think of it, if the client could detect the server's bool out
> config, it could override the server in that instance since it would
> know what it was looking for, so perhaps they could coexist.

I think Pavel has a patch in that is meant to sync variables between client
and server. Perhaps we can use the same facility?

>
> --
> Thom

> On Aug 20, 2012 6:28 PM, "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
> wrote:
>>
>> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
>>
>> > On occasions I have wanted psql to emit the full 'True'/'False'
>> > words instead of cryptic one-letter t/f, which can get lost on
>> > long rows that get wrapped around on screen. Writing long-winded
>> > CASE expressions to get the effect is too much for small ad-hoc
>> > queries.
>> >
>> > I thought of inventing a data type whose out-function would emit
>> > these strings, and tack a ::mybool to the expression I want
>> > modified. But that would break the applications if somebody pasted
>> > the same query in an application (JDBC or some such that
>> > understands boolean) and expected a boolean data type instead of a
>> > text output of an expression.
>>
>> The type itself does output true/false; it's just psql that uses
>> t/f.
>>
>> test=# select 'true'::boolean::text;
>> text
>> ------
>> true
>> (1 row)
>>
>> test=# select 'false'::boolean::text;
>> text
>> -------
>> false
>> (1 row)
>>
>> That has always seemed quite odd (and occasionally inconvenient) to
>> me.
>
> I think that may be from the cast. I didn't see any transformation in psql.
> Looked like it was raw output from the server.

Right. "t", "f" are generated in backend. See boolout() in
backend/utils/adt/bool.c for more details.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> The type itself does output true/false; it's just psql that uses
> t/f.

No, 't'/'f' is what boolout() returns. The 'true'/'false' results from
casting bool to text are intentionally different --- IIRC, Peter E.
argued successfully that this cast behavior is required by SQL spec.
But we'd already been returning 't'/'f' to applications for far too many
years to change it. (And that argument has not gotten any weaker since
then. Keep in mind that Postgres was returning 't'/'f' for bool years
before the SQL spec even had a boolean type.)

If we're going to do something like this at all, I agree that psql is
the place to do it, not the server. But my beef with this patch is that
it's thinking too small --- why would bool be the only type that
somebody would want to editorialize on the display of? I'd rather see
some general "substitute this for that in display of columns of type X"
feature.

regards, tom lane

On Mon, Aug 20, 2012 at 7:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> The type itself does output true/false; it's just psql that uses
>> t/f.
>
> No, 't'/'f' is what boolout() returns. The 'true'/'false' results from
> casting bool to text are intentionally different --- IIRC, Peter E.
> argued successfully that this cast behavior is required by SQL spec.
> But we'd already been returning 't'/'f' to applications for far too many
> years to change it. (And that argument has not gotten any weaker since
> then. Keep in mind that Postgres was returning 't'/'f' for bool years
> before the SQL spec even had a boolean type.)
>
> If we're going to do something like this at all, I agree that psql is
> the place to do it, not the server. But my beef with this patch is that
> it's thinking too small --- why would bool be the only type that
> somebody would want to editorialize on the display of? I'd rather see
> some general "substitute this for that in display of columns of type X"
> feature.
>
> regards, tom lane

Sorry, was on my phone before and couldn't type the response I wanted to.

I like where your head is with the more general case, and I tried to
think along those lines too, but I could not come up with a grand
unifying way to do even null and boolean together, never mind other
ways that I hadn't even thought about. The boolean case is a single
datatype where the null case crosses all nullable types. With null you
only have to handle one case, with boolean, you have two.

What I settled upon was the simplest way I could think of with the
most flexibility. In my opinion, the utility of this patch outweigh's
the niche implementation. I'd welcome other approaches that covered
this in a more generic way, I just can't think of any that aren't
overly complex.

2012/8/21 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> The type itself does output true/false; it's just psql that uses
>> t/f.
>
> No, 't'/'f' is what boolout() returns. The 'true'/'false' results from
> casting bool to text are intentionally different --- IIRC, Peter E.
> argued successfully that this cast behavior is required by SQL spec.
> But we'd already been returning 't'/'f' to applications for far too many
> years to change it. (And that argument has not gotten any weaker since
> then. Keep in mind that Postgres was returning 't'/'f' for bool years
> before the SQL spec even had a boolean type.)
>
> If we're going to do something like this at all, I agree that psql is
> the place to do it, not the server. But my beef with this patch is that
> it's thinking too small --- why would bool be the only type that
> somebody would want to editorialize on the display of? I'd rather see
> some general "substitute this for that in display of columns of type X"
> feature.
>

can you explain your idea, please? - I can't to imagine any general
solution for other types than "enum" like types (without significant
enhancing of psql scripting possibilities)

Regards

Pavel

> regards, tom lane