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?
Best Answer
NULLIF
, like you already found:.. to replace the empty string with NULL. Strictly speaking, your question asks for more:
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:It should be a bit faster to replace both functions with
CASE
:Related: