Logical data types

This topic describes the logical data types supported in Snowflake.

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

Conversion to Boolean

Explicit conversion

Boolean values can be explicitly converted from text string and numeric values to boolean values by 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.

Implicit conversion

Boolean values can be implicitly converted from text string values to boolean values.

String Conversion:

  • ‘true’ is converted to TRUE.

  • ‘false’ is converted to FALSE.

The string values are not case-sensitive.

Numeric Conversion:

  • Zero (0) is converted to FALSE.

  • Any non-zero value is converted to TRUE.

Conversion from Boolean

Explicit conversion

Boolean values can be cast explicitly to string or numeric values.

String Conversion:

  • TRUE is converted to ‘true’.

  • FALSE is converted to ‘false’.

Numeric Conversion:

  • TRUE is converted to 1.

  • FALSE is converted to 0.

Implicit conversion

String Conversion:

  • TRUE is converted to ‘true’.

  • FALSE is converted to ‘false’.

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 |
+-------+------+------+
Copy

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              |
+-------+------+-------------------+
Copy

Boolean column in predicates:

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

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

Text cast to boolean:

SELECT s, TO_BOOLEAN(s) FROM test_boolean;

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

Number cast to boolean:

SELECT n, TO_BOOLEAN(n) FROM test_boolean;

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

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                 |
+----------------------+
Copy
Language: English