postgresqlnull-coalescing

Confused with Postgres COALESCE retuning 'null'

A postgres table contains a column with the data type:

test_score character varying(10)

The column may contain null values or numeric.

SQL Result
select COALESCE( test_score, '0' ) '' (blank)
select COALESCE( test_score::numeric, 0 ) ERROR: invalid input syntax for type numeric: ""
select COALESCE( test_score::numeric, 0 ) ERROR: invalid input syntax for type numeric: ""
select CASE WHEN test_score = '' THEN 0 ELSE test_score::numeric END works 0 (intended result)
select CASE WHEN test_score IS NULL THEN 0 ELSE test_score::numeric END ERROR: invalid input syntax for type numeric: ""

In Oracle terms, I want NVL( TO_NUMBER( test_score ), 0 ), which would always work (assume test_score holds a number).

What is the correct way to use COALESCE in this scenario?

Solution

  • Simplest way I know of doing this is:

    select coalesce(nullif(trim(test_score), '')::numeric, 0);

    Using functions from here Conditional Expressions.

    Where trim removes any empty spaces from test_score and gets the value to '' in the case where there is no numeric value and then nullif converts the value to NULL which is cast to numeric which in turn coalesce converts to 0.