How can I average all bands in a single PostGIS raster table?

I have 1 year of daily climate data in a single raster table in a postgis-enabled postgres database. It has 365 bands (one for each day). How do I average all the bands to get a single annual average per pixel? I tried ST_Union, but it returns all the bands, or I'm not using it correctly:

select rid, st_union(rast, 'MEAN')
from climate_table
group by rid;

I figured out a workaround using ST_DumpAsPolygons, but it is very slow. Any suggestions appreciated. (Also, I can't believe "bands" is not yet a tag, and I don't have enough reputation to create it!)

Solution

According to the documentation he function ST_Union() takes a set of rasters. You have a raster with many bands. You can use ST_Union but you have to transform your data into a set of rasters where each one contains 1 band first.

Here is a testcase:

-- Create a table and add a row with an empty raster:

CREATE TABLE r_src(rid integer primary key, rast raster);
INSERT INTO r_src values(1,ST_MakeEmptyRaster(4,4,0,0,1));

-- Add two band layers to this raster one with value 10 for each point,
-- the other with value 4 for each point.

UPDATE r_src
    SET rast = ST_AddBand(rast,'4BUI'::text,10)
WHERE rid = 1;
UPDATE r_src
    SET rast = ST_AddBand(rast,'4BUI'::text,4)
WHERE rid = 1;

-- check what we have

SELECT  (rmd).width, (rmd).height, (rmd).numbands
FROM (SELECT ST_MetaData(rast) As rmd
    FROM r_src WHERE rid = 1) AS foo;
-- 4| 4| 2  --ok

-- output rasterdata in hex for both bands:

SELECT ST_AsHexWKB(rast) As rastbin FROM r_src WHERE rid=1;


-- output rasterdata for the 2nd band (note the '...040404' point values 
-- compared to '...0A0A0A' if you select the band at idx 1

SELECT ST_AsHexWKB(ST_Band(rast,2))FROM r_src WHERE rid = 1;


-- now step by step...
-- src is the single rast in our source table,
-- in extracted this is joined with a generated series
-- for each value in the series a new raster is returned 
-- containing the band at that index:

WITH src       AS (SELECT rast FROM r_src WHERE rid = 1),
     extracted AS (SELECT ST_Band(src.rast,ser.idx) 
                   FROM src
                   JOIN generate_series(1,2) AS ser(idx ) ON true)
SELECT * FROM extracted;

-- outputs 2 rows with one raster each, and each raster containing one band

-- now aggreagating these:

WITH src       AS (SELECT rast FROM r_src WHERE rid = 1),
     extracted AS (SELECT ST_Band(src.rast,ser.idx) AS r_day
                   FROM src
                   JOIN generate_series(1,2) AS ser(idx ) ON true)
SELECT ST_Union(r_day,1,'MEAN') FROM extracted;

-- outputs one row with the resulting raster holding one band 
-- that band contains the 'MEAN' point values '...070707'

I checked this out with a dataset you linked to and imported it into postgres using raster2pgsql. Due to its size, I decided to split it into tiles of 100x100 and got a table with 45 rows. Now to convert the whole raster I used the following statement:

CREATE TABLE averages AS
WITH extracted AS (SELECT src.rid , ST_Band(src.rast,ser.idx) AS r_day
                   FROM rastertest src
                   JOIN generate_series(1,365) AS ser(idx ) ON true)
SELECT rid, ST_Union(r_day,1,'MEAN') AS rast
FROM extracted
GROUP BY rid;

This creates a new table averages with the average values only.