HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

COALESCE with cast

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
withcoalescecast

Problem

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?

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.