Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 11:04, Alexandre GRAIL
<postgresql.general@augure.net> wrote:

And added to this weirdness is the fact that '1' or '0' (with quote) is OK.

The reason for that at least is that '1' and '0' are valid boolean values.

https://www.postgresql.org/docs/9.5/datatype-boolean.html

There's additional text describing why casts are chosen to be defined
as implicit or not here

https://www.postgresql.org/docs/9.5/typeconv-overview.html

My own opinion is that non-0 should implicitly cast as true and 0
should cast as false. I just run

UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
SELECT c.oid
FROM pg_cast c
inner join pg_type src ON src.oid = c.castsource
inner join pg_type tgt ON tgt.oid = c.casttarget
WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE '%int%')
OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE 'bit%')
OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE 'bool%')
OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE '%int')
);

when I install the system to solve this for my own uses.

Geoff

Geoff Winkless schrieb am 24.01.2019 um 12:45:

The reason for that at least is that '1' and '0' are valid boolean values.

https://www.postgresql.org/docs/9.5/datatype-boolean.html

There's additional text describing why casts are chosen to be defined
as implicit or not here

https://www.postgresql.org/docs/9.5/typeconv-overview.html

My own opinion is that non-0 should implicitly cast as true and 0
should cast as false.

I strongly disagree - that would mimic MySQL's idiosyncrasies and would make such a query valid:

delete from orders
where 42;

On Thu, 24 Jan 2019 at 12:17, Thomas Kellerer <spam_eater@gmx.net> wrote:

Geoff Winkless schrieb am 24.01.2019 um 12:45:

My own opinion is that non-0 should implicitly cast as true and 0
should cast as false.

I strongly disagree - that would mimic MySQL's idiosyncrasies and would make such a query valid:

Feel free. I said it's my own opinion and gave a way for someone who
agrees with me to do the same as I do.

If your objection is that someone can write a stupid query and it
might go wrong, there are a million other things that should be
addressed before implicit int::bool casts.

Geoff

On 1/24/19 5:04 AM, Alexandre GRAIL wrote:

Hello the list,

Maybe this question has been debated before (I didn't find anything
helpful) but :

Why the default is to throw an error when casting Integer to Boolean in
assignment, and accepting it everywhere else ?

So you can type :

postgres=# select 1::boolean;
 bool
------
 t
(1 row)

or

postgres=# select 0::boolean;
 bool
------
 f
(1 row)

But you *cannot* use 1 or 0 as valid input for boolean type when inserting
or updating :

test=# CREATE TABLE test1 (a boolean);
CREATE TABLE
test=# INSERT INTO test1 VALUES (1);
ERROR:  column "a" is of type boolean but expression is of type integer
LINE 1: INSERT INTO test1 VALUES (1);
                                  ^
HINT:  You will need to rewrite or cast the expression.

This behavior cannot be changed, as this cast is hard coded with
"Implicit?=no".

And added to this weirdness is the fact that '1' or '0' (with quote) is OK.

So is there a reason to forbid 0 and 1 as valid boolean, without explicit
cast ?

I'm confused at the question.  You are happily casting 1 and 0 to boolean in
the SELECT statements, and then grumbling when *not* casting them in the
INSERT statements.  Thus, why aren't you casting during the INSERT statements?

test=# INSERT INTO test1 VALUES (1::boolean);

--
Angular momentum makes the world go 'round.

On Thu, Jan 24, 2019 at 4:04 AM Alexandre GRAIL
<postgresql.general@augure.net> wrote:

But you *cannot* use 1 or 0 as valid input for boolean type when
inserting or updating :

test=# CREATE TABLE test1 (a boolean);
CREATE TABLE
test=# INSERT INTO test1 VALUES (1);
ERROR: column "a" is of type boolean but expression is of type integer
LINE 1: INSERT INTO test1 VALUES (1);
^
HINT: You will need to rewrite or cast the expression.

This behavior cannot be changed, as this cast is hard coded with
"Implicit?=no".

And added to this weirdness is the fact that '1' or '0' (with quote) is OK.

Because '1' is the literal character 1 with an unknown type and so can
be (must be) assigned its initial real type from context.

1 is an integer, which is a real type

So is there a reason to forbid 0 and 1 as valid boolean, without
explicit cast ?

To assist developers in avoiding the writing of buggy queries.

David J.

On Thu, 24 Jan 2019 at 14:28, David G. Johnston
<david.g.johnston@gmail.com> wrote:

To assist developers in avoiding the writing of buggy queries.

Amazing how many of these developers find this a hindrance. If only
they could see how helpful we're being to them.

Geoff

On 1/24/19 9:05 AM, Geoff Winkless wrote:

On Thu, 24 Jan 2019 at 14:28, David G. Johnston
<david.g.johnston@gmail.com> wrote:

To assist developers in avoiding the writing of buggy queries.

Amazing how many of these developers find this a hindrance. If only
they could see how helpful we're being to them.

It's the C vs. Ada/Pascal debate, 35 years later...

--
Angular momentum makes the world go 'round.

Geoff Winkless <pgsqladmin@geoff.dj> writes:

On Thu, 24 Jan 2019 at 14:28, David G. Johnston
<david.g.johnston@gmail.com> wrote:

To assist developers in avoiding the writing of buggy queries.

Amazing how many of these developers find this a hindrance. If only
they could see how helpful we're being to them.

People don't generally post to the lists after a type-mismatch error
catches a typo for them. So it's pretty hard to tell about "how
many" developers would find one behavior more useful than the other.
It is safe to say, though, that the same developer complaining today
might have their bacon saved tomorrow.

regards, tom lane

On 1/24/19 3:04 AM, Alexandre GRAIL wrote:

Hello the list,

Maybe this question has been debated before (I didn't find anything
helpful) but :

Why the default is to throw an error when casting Integer to Boolean in
assignment, and accepting it everywhere else ?

The overall reason:

https://www.postgresql.org/docs/8.3/release-8-3.html

E.24.2.1. General

Non-character data types are no longer automatically cast to TEXT
(Peter, Tom)

Previously, if a non-character value was supplied to an operator or
function that requires text input, it was automatically cast to text,
for most (though not all) built-in data types. This no longer happens:
an explicit cast to text is now required for all non-character-string
types. For example, these expressions formerly worked:

So you can type :

postgres=# select 1::boolean;
 bool
------
 t
(1 row)

or

postgres=# select 0::boolean;
 bool
------
 f
(1 row)

But you *cannot* use 1 or 0 as valid input for boolean type when
inserting or updating :

test=# CREATE TABLE test1 (a boolean);
CREATE TABLE
test=# INSERT INTO test1 VALUES (1);
ERROR:  column "a" is of type boolean but expression is of type integer
LINE 1: INSERT INTO test1 VALUES (1);
                                  ^
HINT:  You will need to rewrite or cast the expression.

This behavior cannot be changed, as this cast is hard coded with
"Implicit?=no".

And added to this weirdness is the fact that '1' or '0' (with quote) is OK.

So is there a reason to forbid 0 and 1 as valid boolean, without
explicit cast ?

Thanks!

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 24 Jan 2019 at 15:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:

People don't generally post to the lists after a type-mismatch error
catches a typo for them. So it's pretty hard to tell about "how
many" developers would find one behavior more useful than the other.
It is safe to say, though, that the same developer complaining today
might have their bacon saved tomorrow.

I've missed off WHERE clauses on a live database (oops) in my time,
and I'm happy to see work being done to safeguard against that
(although I tend to be of the opinion that it's not something you ever
do twice!) but I can confidently state that I've never once been
caught out by being surprised that a number was treated as a boolean.

How could you even write a query like the one Thomas posted? It
doesn't even look remotely sensible.

But I have been caught out by boolean vs int, enough that I bothered
to search out that ALTER statement. And I'm a lazy person at heart, so
if something irritated me enough to bother doing that, you can be sure
it was _really_ irritating me.

Geoff

On 1/24/19 7:21 AM, Geoff Winkless wrote:

On Thu, 24 Jan 2019 at 15:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:

People don't generally post to the lists after a type-mismatch error
catches a typo for them. So it's pretty hard to tell about "how
many" developers would find one behavior more useful than the other.
It is safe to say, though, that the same developer complaining today
might have their bacon saved tomorrow.

I've missed off WHERE clauses on a live database (oops) in my time,
and I'm happy to see work being done to safeguard against that
(although I tend to be of the opinion that it's not something you ever
do twice!) but I can confidently state that I've never once been
caught out by being surprised that a number was treated as a boolean.

How could you even write a query like the one Thomas posted? It
doesn't even look remotely sensible.

create table delete_test(id integer);

insert into delete_test values (2), (3), (4);

delete from delete_test where 1::boolean;

DELETE 3

select * from delete_test ;

id

----

(0 rows)

But I have been caught out by boolean vs int, enough that I bothered
to search out that ALTER statement. And I'm a lazy person at heart, so
if something irritated me enough to bother doing that, you can be sure
it was _really_ irritating me.

Geoff

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 24 Jan 2019 at 15:28, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 1/24/19 7:21 AM, Geoff Winkless wrote:

How could you even write a query like the one Thomas posted? It
doesn't even look remotely sensible.

delete from delete_test where 1::boolean;

*chuckle*

You misunderstand me.

I mean, how can one write a query like that by mistake?

DELETE FROM <tablename> WHERE <integer>;

What would you be thinking that that ought to do?

G

On Thu, 24 Jan 2019 at 15:32, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

DELETE FROM <tablename> WHERE <integer>;

What would you be thinking that that ought to do?

To be fair, I suppose that accidentally missing out a test but
including an integer field

DELETE FROM <tablename> WHERE <integerfieldname>;

could do this. Not something I've ever done, but at least I see how
it's possible.

*shrug* I should reiterate, it's just my opinion, I'm certainly not
arguing for it to be changed, although I would be pretty upset if the
existing ability to change the behaviour were removed.

Geoff

On 1/24/19 7:32 AM, Geoff Winkless wrote:

On Thu, 24 Jan 2019 at 15:28, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 1/24/19 7:21 AM, Geoff Winkless wrote:

How could you even write a query like the one Thomas posted? It
doesn't even look remotely sensible.

delete from delete_test where 1::boolean;

*chuckle*

You misunderstand me.

I mean, how can one write a query like that by mistake >
DELETE FROM <tablename> WHERE <integer>;

What would you be thinking that that ought to do?

Getting in a hurry/distracted.

I wrote out the above test case in psql and with tab completion it is
easy to get to:

delete from delete_test where

and then forget the 'field =' part. Though my more common mistake along
that line is:

delete from delete_test;

At any rate, if it can be done it will be done.

G

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 24 Jan 2019 at 15:40, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

delete from delete_test where

and then forget the 'field =' part. Though my more common mistake along
that line is:

delete from delete_test;

At any rate, if it can be done it will be done.

If you follow that logic, then having a single boolean test at all
should be invalid.

CREATE TABLE mytest (myval char (1));
INSERT INTO mytest VALUES ('a'),('b'),('c'),('s'),('t');
DELETE FROM mytest WHERE 't';
SELECT * FROM mytest;
myval
-------
(0 rows)

Geoff

On 1/24/19 7:48 AM, Geoff Winkless wrote:

On Thu, 24 Jan 2019 at 15:40, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

delete from delete_test where

and then forget the 'field =' part. Though my more common mistake along
that line is:

delete from delete_test;

At any rate, if it can be done it will be done.

If you follow that logic, then having a single boolean test at all
should be invalid.

CREATE TABLE mytest (myval char (1));
INSERT INTO mytest VALUES ('a'),('b'),('c'),('s'),('t');
DELETE FROM mytest WHERE 't';
SELECT * FROM mytest;
myval
-------
(0 rows)

People are going to make mistakes that is a given. Eliminating a boolean
test is not going to change that. Where this particular sub-thread
started was with this from a previous post of yours:

"My own opinion is that non-0 should implicitly cast as true and 0
should cast as false. ..."

That opens an infinite number of values that could be seen as True. That
in turn leads to greater chance of fat-thumbing yourself into an oops.
Like you say it is a matter of opinion. The projects opinion is here:

https://www.postgresql.org/docs/11/datatype-boolean.html

and it works for me.

Geoff

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 24 Jan 2019 at 16:00, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

People are going to make mistakes that is a given. Eliminating a boolean
test is not going to change that.

I still think that if you've got to the point where you're actually
part-way through writing a clause you're unlikely to forget to
complete it.

Missing out a clause altogether is understandable but writing half of
one? Even if you weren't sure what the value was you would probably
write

WHERE myfield=

and then have to go and look it up.

Like you say it is a matter of opinion. The projects opinion is here:

https://www.postgresql.org/docs/11/datatype-boolean.html

and it works for me.

And you're welcome to it. I'm not arguing for it changing. I'm simply
stating that I'm very pleased that the default behaviour can be
changed, because in my opinion writing a bunch of explicit casts in a
query is a surefire path to unreadable code.

Geoff

On 24/01/2019 12:45, Geoff Winkless wrote:

My own opinion is that non-0 should implicitly cast as true and 0
should cast as false. I just run

UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
SELECT c.oid
FROM pg_cast c
inner join pg_type src ON src.oid = c.castsource
inner join pg_type tgt ON tgt.oid = c.casttarget
WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE '%int%')
OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE 'bit%')
OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE 'bool%')
OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE '%int')
);

Thanks Geoff for this solution, I was thinking it cannot be changed ! I
end up doing this :

UPDATE pg_cast SET castcontext = 'a' WHERE oid IN (
 SELECT c.oid
 FROM pg_cast c
 inner join pg_type src ON src.oid = c.castsource
 inner join pg_type tgt ON tgt.oid = c.casttarget
 WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%')
);

Only to have 0/1 => bool working in assignment. It saved me from
injecting ALTER TYPE before and after any INSERT/UPDATE.

In my case I don't control the query which is auto generated. (And the
framework assumes 1 and 0 are safe boolean values without cast or quote.
Changing that is not possible.)

On 24/01/2019 17:19, Geoff Winkless wrote:

Like you say it is a matter of opinion. The projects opinion is here:

https://www.postgresql.org/docs/11/datatype-boolean.html

and it works for me.

And you're welcome to it. I'm not arguing for it changing. I'm simply
stating that I'm very pleased that the default behaviour can be
changed, because in my opinion writing a bunch of explicit casts in a
query is a surefire path to unreadable code.

And it may be just a matter of opinion but for me 0 and 1 "naked", *do*
represent boolean value. More than 't' or 'f', if you consider all the
computer history...

In the end it would be very nice to add a pointer in the documentation
about this behavior and a quick workaround for the ones who really need
it. That would be great :D !

Thanks all !

--
Alexandre GRAIL
/Ingénieur Logiciel // Chef de Projet/
*Tél.* +33 6 27 40 77 44
Augure Engineering <https://augure-engineering.fr&gt;