I can get the max value from a raster column using PostGIS and
SELECT max(ST_SummaryStats(rast, TRUE))."max") FROM schema.table;
How do I take that value and get its coordinate(s)?
I don't think that there is a direct way of doing it (maybe with mapAlgebra?), but an easy way would be that once you get the value of your pixel, you should try using ST_PixelOfValue, then use the .x and .y and pass it to ST_RasterToWorldCoord (remember it gives you the upper left point of the pixel, not the center, I don't really understand why). That would be something like that:
WITH max_value as ( SELECT max(ST_SummaryStats(rast, TRUE))."max") as max_value FROM schema.table ), pixel as ( SELECT ST_PixelOfValue(rast, max_value) as pix FROM schema.table, max_value ) SELECT ST_RasterToWorldCoord(rast, pix.x, pix.y) FROM schema.table, pixel;
Also keep in mind that maybe you can have multiple pixels with the same max value.