BOOLEAN Data Type
BOOLEAN can be used as a data type when defining a column in a table or a variable in a database procedure. Support for the BOOLEAN data type helps migrations from other database products.
Boolean columns accept as input the SQL literals FALSE and TRUE. In addition, due to automatic coercion rules, the strings 'FALSE' and 'TRUE' and the integers 0 and 1 are also acceptable for use in a Boolean column or variable. Input is not case sensitive.
The IS Boolean operator can be used in expressions. IS TRUE is true for a BOOLEAN TRUE, IS FALSE is true for a BOOLEAN FALSE, IS UNKNOWN is true for an Unknown (NULL) value. IS UNKNOWN is a synonym for IS NULL when dealing with Boolean values.
ORDER BY BOOLEAN results in grouping rows in this order: FALSE, TRUE, NULL.
The CREATE INDEX statement allows an index to be created on BOOLEAN columns.
Terminal Monitor output for a BOOLEAN column shows the literals FALSE and TRUE as unquoted strings.
CASE expressions can be used with BOOLEAN columns or literals. For example:
CASE expr WHEN cond1 THEN expr2
and
CASE WHEN search_cond1 THEN expr1
accept FALSE or TRUE in condN or search_condN or part thereof, and exprN can include BOOLEAN columns or literals.
The CAST operator supports casting BOOLEAN to and from character types and from the integer values 0 and 1. For example:
• CAST (BOOLEAN AS character_type) is allowed.
• CAST(character_type AS BOOLEAN) is accepted if the character type is the string 'FALSE' or 'TRUE', regardless of case.
• CAST(integer_constant AS BOOLEAN) is accepted for values 0 and 1.
• CAST(integer_expression AS BOOLEAN) is accepted if the integer expression has the value 0 or 1.
(Casting from 0 and 1 is an Ingres extension to the SQL standard, which does not allow it.)
For casting to strings, the data type must be of sufficient length (for example, CHAR(5) for FALSE) or truncation occurs (unless ‑string_truncation=fail is used at connect time). The shortcut CHAR(expr) returns a single character (that is, 'F' or 'T') because it is interpreted as CAST(expr AS CHAR(1)).
Internally, the BOOLEAN type is stored as a single-byte integer that can take only the values 0 and 1.
This feature adds to or changes the syntax of many statements, including ALTER TABLE, COPY TABLE, CREATE INTEGRITY, CREATE TABLE, CREATE TABLE…AS SELECT, DECLARE GLOBAL TEMPORARY TABLE, INSERT INTO, REGISTER TABLE, SELECT, UPDATE, WHERE clause of SELECT, DELETE, UPDATE, and JOIN source ON search_condition.
The BOOLEAN data type is supported in Ingres Star, Ingres Replicator, OpenAPI, embedded SQL, and by Ingres connectivity drivers.
Note: A pre-10.0 client will get an error if it tries to retrieve a result that includes a BOOLEAN column from a 10.0 or higher server.
Here are examples of using the BOOLEAN data type when creating a table or procedure:
CREATE TABLE example (column1 BOOLEAN NOT NULL);
CREATE PROCEDURE example_proc (flag BOOLEAN NOT NULL) AS
DECLARE
var1 BOOLEAN;
BEGIN
...
END;
Here is an example of using the literals FALSE and TRUE in an SQL context:
INSERT INTO example VALUES (FALSE);
UPDATE example SET column1 = TRUE;
SELECT * FROM example WHERE column1 IS TRUE;
...
var1 = TRUE;
WHILE var1 IS NOT FALSE
...
For more information, see these sections in the SQL Reference Guide:
• BOOLEAN Data Type
• Storage Formats of Data Types
• BOOLEAN Literals
• Data Type Conversion Functions
• IS TRUE, IS FALSE, IS UNKNOWN Predicates
• Default Clause