Cleaning geometries in PostGIS?

You can try ST_CollectionExtract to extract [Multi]Polygons from GeometryCollections. Use ST_Multi to force them as MuliPolygons.

UPDATE public.valid_lcmsouthshapefile
  SET geom=ST_Multi(ST_CollectionExtract(ST_MakeValid(geom), 3))
  WHERE NOT ST_IsValid(geom);

After your done, use a CHECK constraint to ensure they stay valid. See details here.

If you only want Polygons or Multipolygons from ST_MakeValid you can use ST_Dump to extract the constituent geometries and then test for the geometry type. ST_MakeValid will sometimes produce Points or LineStrings which is where the GeometryCollection is coming from. Try something like:

SELECT 
  g.geom, 
  row_number() over() AS gid,
FROM 
  (SELECT 
     (ST_DUMP(ST_MakeValid (geom))).geom FROM your_table
  ) AS g
WHERE ST_GeometryType(g.geom) = 'ST_MultiPolygon' 
   OR ST_GeometryType(g.geom) = 'ST_Polygon';

You could use an IN clause instead of OR condition, though the result and query plan would be the same. If you only want Multipolygons you can wrap ST_Dump in the ST_Multi function.

The row_number() over() will simply return you a unique id, starting from one, for each geometry returned from ST_Dump. You can also use the path element returned by ST_Dump, with the same result.

You will presumably want to combine this with a CREATE TABLE cleaned_geoms AS SELECT .... type statement, as a direct update is unlikely to work as ST_MakeValid wil notl generally (or always) produce a one to one mapping from I put to output.

This is untested as I have no means, currently, so there might be a misplaced parenthesis, but the general principle is sound. Hope this helps.