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
(like you added in your answer) 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: