Return to Question

tags, format, typos

Source Link

edited Apr 25, 2018 at 16:27

182.2k
28
457
620

postgres coalesce COALESCE with cast

I'm pulling data from a jsonb column in postgresPostgres, and casting it to an integerinteger to compare it against a count. The problem is that in some cases the data apparently can't be cast to an integer, and I get SQL Error [22P02]: ERROR: invalid input syntax for 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'

isIs there a way to failfall back to 0 if the cast doesn't work?

postgres coalesce with cast

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 an 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 fail back to 0 if the cast doesn't work?

COALESCE with cast

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?

Source Link

asked Apr 24, 2018 at 20:03

user101289

postgres coalesce with cast

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 fail back to 0 if the cast doesn't work?

postgresql cast coalesce