How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?

Robust String to Integer Casting with Error Handling in PostgreSQL

Learn how to safely convert string values to integers in PostgreSQL, ensuring that invalid inputs gracefully default to 0 instead of causing errors.

When working with databases, it's common to encounter scenarios where data stored as strings needs to be used as numerical values. PostgreSQL offers powerful casting capabilities, but direct casts can fail if the string doesn't represent a valid number. This article explores various methods to cast strings to integers in PostgreSQL, specifically focusing on how to handle conversion errors by defaulting to 0.

The Challenge of Direct Casting

Directly casting a string to an integer in PostgreSQL is straightforward using the ::integer syntax or the CAST() function. However, this approach is brittle. If the string contains non-numeric characters or is empty, the cast will fail, resulting in an error that can halt your query or application. This is often undesirable in production environments where data quality might be inconsistent.

-- This will work for valid numbers
SELECT '123'::integer;
-- Result: 123

-- This will cause an error
SELECT 'abc'::integer;
-- ERROR: invalid input syntax for type integer: "abc"

Demonstration of direct casting and its error behavior.

Method 1: Using NULLIF and COALESCE with REGEXP_REPLACE

One robust way to handle invalid string-to-integer conversions is to first clean the string, then attempt the cast, and finally use NULLIF and COALESCE to provide a default value. The REGEXP_REPLACE function can be used to remove any non-digit characters, making the string safe for casting. If, after cleaning, the string is empty, NULLIF will convert it to NULL, which COALESCE can then replace with 0.

SELECT
    input_string,
    COALESCE(NULLIF(REGEXP_REPLACE(input_string, '[^0-9]+', '', 'g'), ''), '0')::integer AS safe_integer_cast
FROM (
    VALUES
        ('123'),
        ('abc'),
        ('45def6'),
        (''),
        (NULL),
        ('  789  ')
) AS data(input_string);

Casting with REGEXP_REPLACE, NULLIF, and COALESCE.

flowchart TD
    A[Input String] --> B{Remove Non-Digits (REGEXP_REPLACE)}
    B --> C{Is Result Empty? (NULLIF)}
    C -- Yes --> D[NULL]
    C -- No --> E[Attempt Cast to Integer]
    D --> F{Coalesce with 0}
    E --> F
    F --> G[Output Integer (0 on error)]

Flowchart for string to integer conversion with REGEXP_REPLACE and COALESCE.

Method 2: Using TRY_CAST (PostgreSQL 10+)

For PostgreSQL versions 10 and above, the TRY_CAST function (available through the pg_try_cast extension or by defining a custom function) provides a cleaner way to handle casting errors. TRY_CAST returns NULL if the cast fails, which can then be easily handled by COALESCE to provide a default value like 0.

-- First, define a custom function for TRY_CAST behavior
CREATE OR REPLACE FUNCTION try_cast_to_int(text) RETURNS integer AS $$
BEGIN
    RETURN $1::integer;
EXCEPTION
    WHEN invalid_text_representation THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Now use the custom function with COALESCE
SELECT
    input_string,
    COALESCE(try_cast_to_int(input_string), 0) AS safe_integer_cast
FROM (
    VALUES
        ('123'),
        ('abc'),
        ('456'),
        (''),
        (NULL)
) AS data(input_string);

Implementing TRY_CAST behavior with a custom PL/pgSQL function.

Method 3: Using CASE with SIMILAR TO or LIKE (Less Robust)

While less robust than regular expressions for complex patterns, for simple cases where you only expect digits, you can use a CASE statement with SIMILAR TO or LIKE to check if a string consists solely of digits before attempting a cast. This method can be simpler for very strict input requirements but might miss edge cases like leading/trailing spaces or signs.

SELECT
    input_string,
    CASE
        WHEN input_string SIMILAR TO '[0-9]+' THEN input_string::integer
        ELSE 0
    END AS safe_integer_cast
FROM (
    VALUES
        ('123'),
        ('abc'),
        ('456'),
        (''),
        (NULL),
        ('  789  ') -- This will fail SIMILAR TO '[0-9]+'
) AS data(input_string);

Using CASE with SIMILAR TO for conditional casting.