This raster question comes up quite a bit on PostGIS mailing lists and stack overflow and the best answer often involves
the often forgotten ST_Reclass function that has existed since PostGIS 2.0.
People often resort to the much slower though more flexible ST_MapAlgebra or dumping out
their rasters as Pixel valued …
PostGIS raster has so so many functions and probably at least 10 ways of doing something some much much slower than others. Suppose
you have a raster, or you have a raster area of interest -- say elevation raster for example, and you want to know the distinct pixel values in the area.
The temptation is to reach for …