Postgres NULLIF greater than x
I'm writing a query to aggregate some data and in my select statement I want to write this:
select coalesce(nullif(sum(field)), '> 1'), 1) from ...
Which, if NULLIF was capable of it, would give me the sum of field unless it was greater than 1, in which case it would give me 1. Of course, I'm asking the impossible of NULLIF, which is intended to do a basic string comparison only.
The reason I don't want to use a CASE statement is because where I have substituted field is an enormous expression which I would have to write out twice if I were to do:
select case when sum(field) > 1 then 1 else sum(field) from ...
I was hoping postgres has an ace up its sleeve and I wouldn't have to do that. Anyone come across this dilemma before?
So you want the minimum of the sum and 1? That't the LEAST function:
select LEAST(sum(field), 1) from ...