TRY_CAST_BOOL

Attempt to cast a value to BOOLEAN, returning NULL on failure instead of raising an error.

Category: miscReturns: BOOLEANDialect: PostgreSql

Syntax

TRY_CAST_BOOL(expr)

Description

## Overview Attempts to convert the input value to BOOLEAN. Returns the parsed value on success and NULL on failure, instead of raising the type error that a regular CAST would produce. Use this function when parsing imported CSV or JSON flag columns where the input format is not fully under your control. ## Behavior - Returns NULL if the input is NULL. - Returns NULL if the input cannot be parsed as a recognized boolean literal. - String parsing is case insensitive. - Recognized true tokens: `true`, `t`, `yes`, `y`, `1`. - Recognized false tokens: `false`, `f`, `no`, `n`, `0`. - Leading and trailing whitespace is tolerated. - Deterministic and side effect free. ## Compatibility - Mirrors the PG-compat TRY_CAST semantics for boolean; the function form is provided for convenience in pipelines that chain multiple TRY_CAST_* calls.

Parameters

Name	Type	Description
`expr`		Specifies the value to attempt to cast to BOOLEAN. Recognized string tokens include 'true'/'false', 'yes'/'no', 't'/'f', and '1'/'0' (case insensitive).

Examples

-- Canonical true
SELECT TRY_CAST_BOOL('true');  -- true

-- Numeric-style true
SELECT TRY_CAST_BOOL('1');  -- true

-- Unrecognized token returns NULL
SELECT TRY_CAST_BOOL('maybe');  -- NULL

-- NULL input returns NULL
SELECT TRY_CAST_BOOL(NULL);  -- NULL

-- Safe parsing of a mixed-format flag column
SELECT id, TRY_CAST_BOOL(active_flag) AS is_active
FROM stage.raw.records
WHERE TRY_CAST_BOOL(active_flag) IS NOT NULL;

-- Use alongside COALESCE to default unknown values to FALSE
SELECT COALESCE(TRY_CAST_BOOL(raw_flag), FALSE) AS is_active
FROM stage.raw.records;

Pitfalls

TRY_CAST_BOOL returns NULL on unrecognized input; it does not raise. Use IS NULL in a downstream filter if you want to quarantine those rows rather than silently keep them.
Numeric values other than 0 and 1 return NULL, not TRUE. `TRY_CAST_BOOL(2)` is NULL, which differs from lenient parsers that treat any non-zero as true.
Locale-specific true/false tokens (for example, 'vrai', 'falso') are not recognized. Translate upstream if your source data uses them.
The function accepts leading/trailing whitespace but rejects embedded whitespace; 'yes sir' returns NULL.