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 '18 at 20:23

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, privacy policy and cookie policy

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