Search code examples
sqlpostgresqlcoalesce

Postgresql COALESCE with String Literals does not work as expected

I'm trying to output and label a column total from a rollup.

select coalesce(column_1, 'Total') as coalesced_value,     
sum(column_2) as column_sum
from table 
where yada yada
group by rollup(coalesced_value)
order by coalesced_value

The query works fine and generates a total as expected, but the column value that I would expect to be 'Total' shows up as [null].

There's a good chance it's probably some lack of understanding on my part, but then there's this that says that PostgreSQL's COALESCE is non-standard and makes me wonder if it's really me.

To quote:

COALESCE on row types

The spec defines COALESCE(X,Y) as a syntactic transformation to CASE WHEN X IS NOT NULL THEN X ELSE Y END (it leaves open the question of whether X is really evaluated twice by disallowing non-deterministic expressions or expressions with side effects in this context). A consequence of this is that the rather odd rules for null tests of row types are applied. PostgreSQL applies only the "is not the null value" test to X. Accordingly, if X is a row value containing null columns, PostgreSQL will return X, while the spec would require returning Y.

(Lovely explanation, right?)

I also ran across some information that indicates that COALESCE data types have to match, otherwise the function fails silently. (!)

I would expect the string literal 'Total' to be interpreted as a varchar, and column_1 is defined in the database as a varchar(12), but at this point I am not sure of much of anything, and any help would be most appreciated.

Solution

  • The problem is this:

    group by rollup(coalesced_value)

    You are grouping by the value after the coalesce() is applied. Hence, the rollup is then generating the null value.

    Instead, group by the column that is in the data:

    select coalesce(column_1, 'Total') as coalesced_value,     
       sum(column_2) as column_sum
from table 
where yada yada
group by rollup(column_1)
order by (column_1 is null)::int, -- put the total at the end
         coalesced_value;

    This explicitly orders the results in the way that you want them. The results will be what you want without it (I think). The first part (column1 is null)::int is a boolean expression converted to a number. Null values are true and assigned a value of 1; non-NULL values are assigned a value of 0. These are first, because the sort is ascending (by default).