Menu
I prefer 1/0 instead of t/f, so what should I use when converting boolean to integer?
select coalesce((null::boolean)::int, 0)
OR
select case null::boolean when 't' then 1 else 0 end
... something else?
688
PostgreSQL supports a single Boolean data type: BOOLEAN that can have three values: true , false and NULL . PostgreSQL uses one byte for storing a boolean value in the database. The BOOLEAN can be abbreviated as BOOL . In standard SQL, a Boolean value can be TRUE , FALSE , or NULL .
The key words TRUE and FALSE are the preferred ( SQL -compliant) method for writing Boolean constants in SQL queries. But you can also use the string representations by following the generic string-literal constant syntax described in Section 4.1. 2.7, for example 'yes'::boolean .
The difference is in how NULL values are handled. Per the docs: IS FALSE will always return a boolean value, even if the argument is null. = 'f' will return null if the argument is null.
PostgreSQL provides the standard SQL type boolean; see Table 8-19. The boolean type can have several states: "true", "false", and a third state, "unknown", which is represented by the SQL null value.
Regardless of which you do, a Boolean null does not equal false, any more than a numeric null equals zero.
Try:
Cast(col1 as integer)
If you really wanted to treat null as false then:
case when col1 then 1 else 0 end
It would be a Bad Thing though
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
