Re: Casting Integer to Boolean in assignment

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