patternsqlMinor
COALESCE with cast
Viewed 0 times
withcoalescecast
Problem
I'm pulling data from a
I've tried both of these, and they both work for the first 400-500 rows until it hits a row with bad data:
Is there a way to fall back to
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?Solution
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'Code Snippets
select COALESCE(NULLIF(raw_record->>'ObjectCount',''),0)::int
from resources where record_id = '274015000000'Context
StackExchange Database Administrators Q#204844, answer score: 2
Revisions (0)
No revisions yet.