Skip to main content


Showing posts from March, 2014

How to get non zero min value from MIN(0, 1, 2)


Today, i have faced an interesting problem like below.

I want to get MIN(UNNEST(ARRAY[0, 1, 2, ....]))  as non-zero small element. In this case, it's 1.

Below is my problem description.

postgres=# SELECT SUM(val), MAX(val), MIN(val) FROM ( SELECT UNNEST(ARRAY[1, 2, 3]) val UNION ALL --Appending some dummy rows, for getting what i would like to expect. SELECT UNNEST(ARRAY[0, 0, 0]) val ) AS FOO;  sum | max | min -----+-----+-----    6 |  3  | 0 (1 row)
As you see, i can able to identify the sum, max without any problem. But when it comes to "min", i am getting the value as 0. But, I want the minimum as 1 as per my requirement. I can able to get the min, max, sum from the first array it self. But, my implementation doesn't allow this. :(

I have tried it in so many ways, and finally found the following solution. I believe, there will be some better ways also, but just wanted to keep a note on this.
postgres=# SELECT SUM(val), MAX(val),  COALESCE((SELECT * FROM UN…