Logical Data Types

This topic describes the logical data types supported in Snowflake.

In this Topic:

Data Types

Snowflake supports a single logical data type, BOOLEAN.

BOOLEAN

BOOLEAN can have TRUE or FALSE values. BOOLEAN can also have an “unknown” value, which is represented by NULL. Boolean columns can be used in expressions (e.g. SELECT list), as well as predicates (e.g. WHERE clause).

The BOOLEAN data type enables support for Ternary Logic.

Boolean Conversion

Boolean values can be explicitly converted from text string and numeric values using the TO_BOOLEAN or CAST , :: functions:

String Conversion:
 
  • Strings converted to TRUE: 'true', 't', 'yes', 'y', 'on', '1'.
  • Strings converted to FALSE: 'false', 'f', 'no', 'n', 'off', '0'.
  • Conversion is case-insensitive.
  • All other text strings cannot be converted to Boolean values.
Numeric Conversion:
 
  • Zero (0) is converted to FALSE.
  • Any non-zero value is converted to TRUE.

BOOLEAN types can also be implicitly converted to strings:

  • TRUE is converted to ‘true’.
  • FALSE is converted to ‘false’.
  • Conversion is always all lowercase.

Examples

CREATE OR REPLACE TABLE test_boolean(
   b BOOLEAN,
   n NUMBER,
   s STRING);

INSERT INTO test_boolean VALUES (true, 1, 'yes'), (false, 0, 'no'), (null, null, null);

SELECT * FROM test_boolean;

+-------+------+------+
| B     |    N | S    |
|-------+------+------|
| True  |    1 | yes  |
| False |    0 | no   |
| NULL  | NULL | NULL |
+-------+------+------+

Boolean-typed expression:

SELECT b, n, NOT b AND (n < 1) FROM test_boolean;

+-------+------+-------------------+
| B     |    N | NOT B AND (N < 1) |
|-------+------+-------------------|
| True  |    1 | False             |
| False |    0 | True              |
| NULL  | NULL | NULL              |
+-------+------+-------------------+

Boolean column in predicates:

SELECT * FROM test_boolean WHERE NOT b AND (n < 1);

+-------+---+----+
| B     | N | S  |
|-------+---+----|
| False | 0 | no |
+-------+---+----+

Text cast to boolean:

SELECT s, TO_BOOLEAN(s) FROM test_boolean;

+------+---------------+
| S    | TO_BOOLEAN(S) |
|------+---------------|
| yes  | True          |
| no   | False         |
| NULL | NULL          |
+------+---------------+

Number cast to boolean:

SELECT n, TO_BOOLEAN(n) FROM test_boolean;

+------+---------------+
|    N | TO_BOOLEAN(N) |
|------+---------------|
|    1 | True          |
|    0 | False         |
| NULL | NULL          |
+------+---------------+

Boolean implicitly converted to text:

SELECT 'Text for ' || s || ' is ' || b AS result FROM test_boolean;

+----------------------+
| RESULT               |
|----------------------|
| Text for yes is true |
| Text for no is false |
| NULL                 |
+----------------------+