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?