BOOLEAN data type
A data type used in CREATE TABLE
and ALTER TABLE
statements, representing a single true/false choice.
Syntax: In the column definition of a CREATE TABLE
statement:
column_name BOOLEAN
Range: TRUE
or FALSE
. Do not use quotation marks around the
TRUE
and FALSE
literal values. You can write the literal values in
uppercase, lowercase, or mixed case. The values queried from a table are always returned in lowercase,
true
or false
.
Conversions: Impala does not automatically convert any other type to BOOLEAN
. All
conversions must use an explicit call to the CAST()
function.
You can use CAST()
to convert
any integer or floating-point type to
BOOLEAN
: a value of 0 represents false
, and any non-zero value is converted
to true
.
SELECT CAST(42 AS BOOLEAN) AS nonzero_int, CAST(99.44 AS BOOLEAN) AS nonzero_decimal,
CAST(000 AS BOOLEAN) AS zero_int, CAST(0.0 AS BOOLEAN) AS zero_decimal;
+-------------+-----------------+----------+--------------+
| nonzero_int | nonzero_decimal | zero_int | zero_decimal |
+-------------+-----------------+----------+--------------+
| true | true | false | false |
+-------------+-----------------+----------+--------------+
When you cast the opposite way, from BOOLEAN
to a numeric type,
the result becomes either 1 or 0:
SELECT CAST(true AS INT) AS true_int, CAST(true AS DOUBLE) AS true_double,
CAST(false AS INT) AS false_int, CAST(false AS DOUBLE) AS false_double;
+----------+-------------+-----------+--------------+
| true_int | true_double | false_int | false_double |
+----------+-------------+-----------+--------------+
| 1 | 1 | 0 | 0 |
+----------+-------------+-----------+--------------+
You can cast DECIMAL
values to BOOLEAN
, with the same treatment of zero and
non-zero values as the other numeric types. You cannot cast a BOOLEAN
to a
DECIMAL
.
You cannot cast a STRING
value to BOOLEAN
, although you can cast a
BOOLEAN
value to STRING
, returning '1'
for
true
values and '0'
for false
values.
Although you can cast a TIMESTAMP
to a BOOLEAN
or a
BOOLEAN
to a TIMESTAMP
, the results are unlikely to be useful. Any non-zero
TIMESTAMP
(that is, any value other than 1970-01-01 00:00:00
) becomes
TRUE
when converted to BOOLEAN
, while 1970-01-01 00:00:00
becomes FALSE
. A value of FALSE
becomes 1970-01-01
00:00:00
when converted to BOOLEAN
, and TRUE
becomes one second
past this epoch date, that is, 1970-01-01 00:00:01
.
NULL considerations: An expression of this type produces a NULL
value if any
argument of the expression is NULL
.
Partitioning:
Do not use a BOOLEAN
column as a partition key. Although you can create such a table,
subsequent operations produce errors:
[localhost:21000] > create table truth_table (assertion string) partitioned by (truth boolean);
[localhost:21000] > insert into truth_table values ('Pigs can fly',false);
ERROR: AnalysisException: INSERT into table with BOOLEAN partition column (truth) is not supported: partitioning.truth_table
Examples:
SELECT 1 < 2;
SELECT 2 = 5;
SELECT 100 < NULL, 100 > NULL;
CREATE TABLE assertions (claim STRING, really BOOLEAN);
INSERT INTO assertions VALUES
("1 is less than 2", 1 < 2),
("2 is the same as 5", 2 = 5),
("Grass is green", true),
("The moon is made of green cheese", false);
SELECT claim FROM assertions WHERE really = TRUE;
HBase considerations: This data type is fully compatible with HBase tables.
Parquet considerations: This type is fully compatible with Parquet tables.
Text table considerations: Values of this type are potentially larger in text tables than in tables using Parquet or other binary formats.
Column statistics considerations: Because this type has a fixed size, the maximum and average size
fields are always filled in for column statistics, even before you run the COMPUTE STATS
statement.
Kudu considerations:
Currently, the data types CHAR
,
ARRAY
, MAP
, and
STRUCT
cannot be used with Kudu tables.