Thread: Casting Integer to Boolean in assignment
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 ? Thanks!
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.
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
Alexandre GRAIL
Ingénieur Logiciel // Chef de Projet
Tél. +33 6 27 40 77 44
Attachment
Alexandre GRAIL wrote: > 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.) You are aware that catalog modifications are not supported, right? One of the reasons is that these modifications will be gone after a major upgrade, and you'll have to remember to re-apply them. Making type casts more liberal increases the risk of ambiguities during type resolution, which cause error messages if PostgreSQL cannot find a single best candidate. So there is a price you are paying - but if it works for you, you probably won't mind. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com