COALESCE with cast

Question

I'm pulling data from a jsonb column in Postgres, and casting it to an integer to compare it against a count. The problem is that in some cases the data apparently can't be cast to integer and I get:

SQL Error [22P02]: ERROR: invalid input syntax for integer: ""

I've tried both of these, and they both work for the first 400-500 rows until it hits a row with bad data:

select coalesce(CAST(raw_record->>'ObjectCount' AS integer), 0) from resources 
where record_id = '274015000000'

select CAST(raw_record->>'ObjectCount' AS integer) from resources 
where record_id = '274015000000'

Is there a way to fall back to 0 if the cast doesn't work?

Answer 1

NULLIF, like you already found:

SELECT NULLIF(raw_record->>'ObjectCount','')::int ...

.. to replace the empty string with NULL. Strictly speaking, your question asks for more:

Is there a way to fall back to 0 if the cast doesn't work?

A more general solution:

• How can I manage an exception generated by Postgis?

COALESCE is unrelated to the question. But adapt slightly what you have in your answer to reduce casting back and forth:

SELECT COALESCE(NULLIF(raw_record->>'ObjectCount','')::int, 0) ...

It should be a bit faster to replace both functions with CASE:

SELECT CASE WHEN raw_record->>'ObjectCount' <> ''  -- excludes null and ''
            THEN (raw_record->>'ObjectCount')::int
            ELSE 0 END ...

Related:

• Best way to check for “empty or null value”

Answer 2

I was on the right track but the wrong train. Here's what ended up working:

select COALESCE(NULLIF(raw_record->>'ObjectCount',''),0)::int 
from resources where record_id = '274015000000'