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

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