Array_agg group by and null

Question

Given this table:

SELECT * FROM CommodityPricing order by dateField "SILVER";60.45;"2002-01-01" "GOLD";130.45;"2002-01-01" "COPPER";96.45;"2002-01-01" "SILVER";70.45;"2003-01-01" "GOLD";140.45;"2003-01-01" "COPPER";99.45;"2003-01-01" "GOLD";150.45;"2004-01-01" "MERCURY";60;"2004-01-01" "SILVER";80.45;"2004-01-01" 

As of 2004, copper was discharged and mercury was introduced.
How can I get the value (array_agg(value order by date desc) ) [1] as NULL for COPPER ?

 select commodity,(array_agg(value order by date desc) ) --[1] from CommodityPricing group by commodity "COPPER";"{99.45,96.45}" "GOLD";"{150.45,140.45,130.45}" "MERCURY";"{60}" "SILVER";"{80.45,70.45,60.45}" 

Answer 1

SQL Fiddle

 select commodity, array_agg( case when commodity = 'COPPER' then null else price end order by date desc ) from CommodityPricing group by commodity ; 

Answer 2

To “skip” missing rows with NULL values ​​in the resulting array, build your query in the full row grid and LEFT JOIN actual grid values. <w> Given this table definition:

 CREATE TEMP TABLE price ( commodity text , value numeric , ts timestamp -- using ts instead of the inappropriate name date ); 

I use generate_series() to get a list of timestamps representing years, and CROSS JOIN for a unique list of all products ( SELECT DISTINCT ... ).

 SELECT commodity, (array_agg(value ORDER BY ts DESC)) AS years FROM generate_series ('2002-01-01 00:00:00'::timestamp , '2004-01-01 00:00:00'::timestamp , '1y') t(ts) CROSS JOIN (SELECT DISTINCT commodity FROM price) c(commodity) LEFT JOIN price p USING (ts, commodity) GROUP BY commodity; 

Result:

 COPPER {NULL,99.45,96.45} GOLD {150.45,140.45,130.45} MERCURY {60,NULL,NULL} SILVER {80.45,70.45,60.45} 

SQL Fiddle
_{I drop the array into the text in the fiddle because the display sucks and swallows the NULL values ​​otherwise.}