Skip to main content
added 302 characters in body
Source Link
Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620

NULLIF, like you already found:

SELECT NULLIF(raw_record->>'ObjectCount','')::int ...

.. to replace the empty string with NULL. Strictly speaking, your question asks for more:

Is there a way to fall back to 0 if the cast doesn't work?

A more general solution:

COALESCE (like you added in your answer) is unrelated to the question. But adapt slightly what you have in your answer to reduce casting back and forth:

SELECT COALESCE(NULLIF(raw_record->>'ObjectCount','')::int, 0) ...

It should be a bit faster to replace both functions with CASE:

SELECT CASE WHEN raw_record->>'ObjectCount' <> ''  -- excludes null and ''
            THEN (raw_record->>'ObjectCount')::int
            ELSE 0 END ...

Related:

NULLIF, like you already found:

SELECT NULLIF(raw_record->>'ObjectCount','')::int ...

COALESCE (like you added in your answer) is unrelated to the question. But adapt slightly to reduce casting back and forth:

SELECT COALESCE(NULLIF(raw_record->>'ObjectCount','')::int, 0) ...

It should be a bit faster to replace both functions with CASE:

SELECT CASE WHEN raw_record->>'ObjectCount' <> ''  -- excludes null and ''
            THEN (raw_record->>'ObjectCount')::int
            ELSE 0 END ...

Related:

NULLIF, like you already found:

SELECT NULLIF(raw_record->>'ObjectCount','')::int ...

.. to replace the empty string with NULL. Strictly speaking, your question asks for more:

Is there a way to fall back to 0 if the cast doesn't work?

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:

SELECT COALESCE(NULLIF(raw_record->>'ObjectCount','')::int, 0) ...

It should be a bit faster to replace both functions with CASE:

SELECT CASE WHEN raw_record->>'ObjectCount' <> ''  -- excludes null and ''
            THEN (raw_record->>'ObjectCount')::int
            ELSE 0 END ...

Related:

Source Link
Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620

NULLIF, like you already found:

SELECT NULLIF(raw_record->>'ObjectCount','')::int ...

COALESCE (like you added in your answer) is unrelated to the question. But adapt slightly to reduce casting back and forth:

SELECT COALESCE(NULLIF(raw_record->>'ObjectCount','')::int, 0) ...

It should be a bit faster to replace both functions with CASE:

SELECT CASE WHEN raw_record->>'ObjectCount' <> ''  -- excludes null and ''
            THEN (raw_record->>'ObjectCount')::int
            ELSE 0 END ...

Related: