PostgreSQL: Cast VARCHAR to Boolean

I’ve dealt with legacy systems that store boolean flags as text—‘yes’ and ’no’, or ‘1’ and ‘0’, or ’enabled’ and ‘disabled’. It works, sort of, until you try to do logical operations or comparisons. You can’t query “WHERE flag = true” on a text column; you need actual boolean values. Converting these text flags to proper booleans is essential for making queries logical and performant.

PostgreSQL makes this straightforward because it recognizes common boolean text representations and can convert them automatically. But you also have options for custom formats that don’t match PostgreSQL’s defaults.

Quick Answer: Use :: BOOLEAN Cast or CASE

To convert text to boolean:

SELECT
    user_id,
    active_text,
    active_text::BOOLEAN as active_bool,
    CASE WHEN active_text IN ('yes', 'y', 'true', '1') THEN true ELSE false END as custom_bool
FROM users
LIMIT 5;

PostgreSQL understands common text representations automatically.

How It Works

The ::BOOLEAN cast recognizes: ’true’, ‘yes’, ‘on’, ‘1’, ‘y’, ’t’ as true; ‘false’, ’no’, ‘off’, ‘0’, ’n’, ‘f’ as false. Case-insensitive. Anything else throws an error.

Different Approaches & When to Use Them

Approach 1: Direct Cast (PostgreSQL Values)

SELECT value::BOOLEAN FROM strings;

Works with standard PostgreSQL boolean values.

Approach 2: Custom Mapping with CASE

SELECT
    CASE
        WHEN status IN ('active', 'enabled', '1', 'yes')
        THEN true
        ELSE false
    END as is_active
FROM data;

Best for: Non-standard text values.

Approach 3: Safe Conversion with Error Handling

SELECT
    CASE
        WHEN value IN ('true', 'yes', '1', 'on')  THEN true
        WHEN value IN ('false', 'no', '0', 'off') THEN false
        ELSE NULL
    END as bool_value
FROM strings;

Returns NULL for unrecognized values.

Real-World Example: Feature Flag Migration

Convert feature flags from text to boolean:

SELECT
    feature_id,
    feature_name,
    enabled_text,
    CASE
        WHEN enabled_text::BOOLEAN THEN 'Enabled'
        ELSE 'Disabled'
    END as feature_status
FROM features
WHERE enabled_text IN ('true', 'false')
ORDER BY feature_name;

FAQ

Q: What text values does PostgreSQL recognize? A: True: ’true’, ‘yes’, ‘on’, ‘1’, ‘y’, ’t’. False: ‘false’, ’no’, ‘off’, ‘0’, ’n’, ‘f’.

Q: What about case sensitivity? A: Case-insensitive: ‘TRUE’ = ’true’ = ‘True’.

Q: What if the value doesn’t match? A: PostgreSQL throws an error. Use CASE to handle custom values.

Q: Can I use COALESCE with boolean conversion? A: Yes: COALESCE(value::BOOLEAN, false) for defaults.

Wrapping Up

Cast text to boolean with ::BOOLEAN for standard PostgreSQL values, or use CASE for custom mappings. PostgreSQL recognizes common boolean text representations automatically.