MySQLism: MySQL considers INT and BOOLEAN the same

SQL-99 standard does not consider that a boolean is an integer with 0 or 1 value. It may also be NULL, which is a third value. Therefore, SQL-99 makes no assumption and considers boolean as a seperate type from integer. Also, a real boolean takes less space on disc than an integer.

Modern languages like Python allow lazy types and this does not beak the internal logic.

MySQL considers that the boolean type is an integer type with value 0 or 1. There is no fundamental breakage in this assumption.

PostgreSQL has a native binary type, which is able to cast automatically into integer and conversely when needed. For some reason, Drupal abstraction layer represents the binary type using an integer. Probably because MySQL does.

Therefore the following query will execute under MySQL and fail under PostgreSQL:

SELECT delta, theme, weight, region, custom, throttle, visibility, pages, title 
FROM blocks 
WHERE 
status 
AND 
module = 'blablabla'

The only reason for this is that Drupal abstraction layer considers that a boolean is an integer. Therefore, the solution is to write an explicit SQL query using 1 for true and 0 for false:

SELECT delta, theme, weight, region, custom, throttle, visibility, pages, title 
FROM blocks 
WHERE 
status =1 
AND 
module = 'blablabla'

PostgreSQL will cast data automatically in this case.

Another possible would be to use PostgreSQL native boolean type in Drupal abstraction layer. I have no idea whether this is planned for D7 or not.

This would permit implicit cast between INT and BOOL. But it would break compatibility with existing PostgreSQL databases or require to change type in existing databases. This is not a lot of work to change a type, as PostgreSQL allows type promotion.

Reference:
http://drupal.org/node/489616