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?

  • Could you add some sample data, just to check what are you trying to accomplish.
    – McNets
    Apr 24, 2018 at 20:23

2 Answers 2


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:

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 ...



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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.