ClickHouse DB::Exception: Cannot parse boolean value

The "DB::Exception: Cannot parse boolean value" error occurs when ClickHouse tries to interpret a string as a Bool type and the value does not match any recognized boolean representation. The CANNOT_PARSE_BOOL error code fires specifically for the Bool data type, which was introduced in ClickHouse as a proper type (backed by UInt8 internally). While ClickHouse accepts several representations of true and false, anything outside that set triggers this error.

Impact

The INSERT or query that hits this error fails completely:

• No rows from the batch are committed to the table.
• This error often catches teams off guard because different systems represent booleans differently, and the mismatch only becomes apparent at insert time.
• Pipelines that ingest data from multiple sources with varying boolean conventions are particularly vulnerable.

Common Causes

1. Unrecognized boolean strings -- values like yes, no, Y, N, on, off, or enabled/disabled that ClickHouse does not recognize as boolean by default.
2. Numeric representations beyond 0/1 -- values like 2, -1, or 99 in a Bool column. ClickHouse only accepts 0 and 1 as numeric booleans.
3. Empty strings -- a missing or empty field in CSV mapped to a non-Nullable Bool column.
4. Case sensitivity issues -- while ClickHouse accepts true, True, and TRUE, some edge cases with mixed casing or extra whitespace can fail.
5. Locale-specific values -- boolean representations in other languages (e.g., vrai/faux, ja/nein).

Troubleshooting and Resolution Steps

1. Check what values ClickHouse accepts for Bool. The accepted values are: true, false, 1, 0, TRUE, FALSE, True, False. The error message will show the rejected value.

2. Map non-standard values in your query. Use a CASE expression or multiIf:

   INSERT INTO my_table
   SELECT
       multiIf(
           lower(raw_bool) IN ('yes', 'y', 'on', '1', 'true'), true,
           lower(raw_bool) IN ('no', 'n', 'off', '0', 'false'), false,
           NULL
       ) AS bool_col
   FROM input('raw_bool String')
   FORMAT CSV
   

3. Use UInt8 instead of Bool if your data has values beyond true/false:

   -- UInt8 is more permissive and accepts any integer 0-255
   ALTER TABLE my_table MODIFY COLUMN flag UInt8;
   

4. Make the column Nullable for missing values:

   ALTER TABLE my_table MODIFY COLUMN is_active Nullable(Bool);
   

5. Preprocess the data to normalize boolean representations:

   # Convert yes/no to true/false
   sed -i 's/\byes\b/true/gi; s/\bno\b/false/gi' data.csv
   

6. Set input_format_csv_empty_as_default so empty fields get the default (false for Bool):

   SET input_format_csv_empty_as_default = 1;
   

7. Use input_format_allow_errors_num to skip rows with unparseable booleans:

   SET input_format_allow_errors_num = 50;
   INSERT INTO my_table FORMAT CSV
   

Best Practices

• Standardize boolean representation across your data sources to true/false or 1/0 before data reaches ClickHouse.
• If you cannot control the source format, use a staging table with String columns and transform values during the load into the final table.
• Consider using UInt8 instead of Bool if your system needs to accommodate a wider range of boolean-like values.
• Document the accepted boolean formats for your pipeline so that data producers know what to emit.
• Test with sample data from every data source before setting up production ingestion.

Frequently Asked Questions

Q: Does ClickHouse accept yes and no as boolean values?
A: No, not by default. The Bool type only accepts true, false, 1, 0, and their case variants (TRUE, False, etc.). You need to map yes/no to true/false in your ETL or query logic.

Q: What is the internal storage of the Bool type?
A: Bool is stored as UInt8 internally, where true maps to 1 and false maps to 0. You can use UInt8 directly if you need more flexibility in accepted input values.

Q: Can I insert the integer 2 into a Bool column?
A: No. Only 0 and 1 are accepted as numeric booleans. Any other integer will trigger the CANNOT_PARSE_BOOL error. If you need to accept arbitrary integers, use UInt8.

Q: How do I handle NULL booleans?
A: Define the column as Nullable(Bool). Then NULL values, empty strings (with appropriate settings), and explicit \N in TSV will be stored as NULL rather than causing a parse error.