EnterpriseDB: Miscellaneous Types Oracle Tips by Burleson Consulting

BOOLEAN

A Boolean value is a TRUE or FALSE indicator.  The only valid values for BOOLEAN are TRUE, FALSE, and NULL.  NULL does not mean either TRUE or FALSE.  It just means that no value has been set.  You may choose to consider a NULL to be false.

Oracle does not support a Boolean data type (in SQL, it does in PL/SQL).  In Oracle, you would generally store Booleans as either NUMBERs or VARCHAR2s.  For example, you may say that in a given column, a 0 means TRUE and any other number means FALSE or vice versa.  Or maybe 1 means TRUE and any other number means FALSE.  Or maybe 0 means TRUE and 1 means FALSE and any other number is invalid.

If you used a VARCHAR2, you could store the letters "TRUE" or "FALSE".  Of course, you could also store "YES" and "NO".  Or "Y" or "N".  How about "T" or "F"?

Do you see where I'm going with this?  A BOOLEAN is a very basic data type.  When each developer/designer is left to decide how to implement a basic type, hilarity can ensue.

The BOOLEAN data type is defined in the SQL standard (although as an optional type).  EnterpriseDB supports the BOOLEAN standard as defined in the SQL standard.  You may reference an EnterpriseDB BOOLEAN as several values but you should try to stick with the TRUE or FALSE syntax.

CREATE TABLE boolean_table (
  bool_true BOOLEAN,
  bool_false BOOLEAN
); 

INSERT INTO boolean_table (bool_true, bool_false)
  VALUES (TRUE, FALSE); 

SELECT * FROM boolean_table; 

edb=# CREATE TABLE boolean_table (
edb(#   bool_true BOOLEAN,
edb(#   bool_false BOOLEAN
edb(# );
CREATE TABLE

edb=#
edb=# INSERT INTO boolean_table (bool_true, bool_false)
edb-#   VALUES (TRUE, FALSE);
INSERT 0 1
edb=#
edb=# SELECT * FROM boolean_table;

 bool_true | bool_false
-----------+------------
 t         | f

(1 row) 

edb=#



This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.