Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL boolean cast (0 as false)

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?

like image 688
mpapec Avatar asked Apr 22 '13 08:04

mpapec


People also ask

Can a boolean be NULL in PostgreSQL?

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 .

How do I declare a boolean in PostgreSQL?

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 .

Is False vs false Postgres?

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.

Is there boolean in Postgres?

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.


1 Answers

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

like image 60
David Aldridge Avatar answered Oct 19 '22 15:10

David Aldridge