Postgresql COALESCE with String Literals does not work as expected

Question

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.

That bit in the wiki is talking about "row types" (things like coalesce(some_row_value, row(1,2,3))) so it doesn't have anything to do with simple varchars, the text makes more sense when you realize that X and Y in that context are compound values. Looks like you're getting the string '[null]' somewhere in your column_1 values so everything is working fine and you're being tricked by an odd value in the column (which probably came from a broken application that was writing data to your table). So look at your table and see if you can produce a minimal example. — mu is too short
– mu is too short, Commented Sep 30, 2018 at 21:09

Gordon Linoff · Accepted Answer · 2018-10-01 01:44:20Z

5

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).

edited Oct 1, 2018 at 1:44

answered Sep 30, 2018 at 21:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gordon Linoff Over a year ago

@klin . . . I prefer to be explicit.

TomEE Over a year ago

This appears to be the best answer. Could you please explain the first term of the order by clause? [(geoid is null)::int, coalesced_value] I'm not that familiar with PostgreSQL notation.

Gordon Linoff Over a year ago

@TomEE . . . It would not work if any values in column_1 were after Total alphabetically.

klin · Accepted Answer · 2018-09-30 21:34:01Z

1

I'm not an expert in POSTGRESQL, but I can still provide some insight. The easiest way would be to simply use a subquery:

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

If you want to avoid a subquery, you should be able to use the GROUPING function. I know this works in Teradata SQL, but after some Googling it appears it should work in POSTGRESQL as well:

select case when grouping(column_1) = 1 then 'Total'
else column_1 end as grouping_value
,sum(column_2) as column_sum
from table
where yada yada
group by rollup(column_1)
order by grouping_value;

edited Sep 30, 2018 at 21:34

klin

123k15 gold badges241 silver badges263 bronze badges

answered Sep 30, 2018 at 21:13

M. Wise

1867 bronze badges

Collectives™ on Stack Overflow

Postgresql COALESCE with String Literals does not work as expected

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related