Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types?
The problem here is that the types aren't mismatched. PostGIS provides very few PostgreSQL types, namely:


box2d — A box composed of x min, ymin, xmax, ymax. Often used to return the 2d enclosing box of a geometry.
box3d — A box composed of x min, ymin, zmin, xmax, ymax, zmax. Often used to return the 3d extent of a geometry or collection of geometries.
geometry — Planar spatial data type.
geometry_dump — A spatial datatype with two fields - geom (holding a geometry object) and path[] (a 1-d array holding the position of the geometry within the dumped object.)
geography — Ellipsoidal spatial data type.


That said, there is clearly no check on different subtypes of geometry.  From point or multipoint, a violation of subtype causes the transaction to fail.

Create an ETL script that loads into a simple of geometry, then you can select the types that are not of the subtype with ST_GeometryType, or GeometryType

CREATE EXTENSION postgis;

CREATE TABLE t (
    id integer,
    p geometry
);

INSERT INTO t
VALUES
    ( 1, ST_GeometryFromText('Point(0 0)')      ),
    ( 2, ST_GeometryFromText('Point(1 2)')      ),
    ( 3, ST_GeometryFromText('MultiPoint(2 3)') ),
    ( 4, ST_GeometryFromText('Point(5 23)')     ),
    ( 5, ST_GeometryFromText('Point(42 36)')    );


Now you can run,

SELECT id, ST_AsText(p)
FROM t
WHERE GeometryType(p) &lt;&gt; 'POINT';


And you'll get,

 id |    st_astext    
----+-----------------
  3 | MULTIPOINT(2 3)
(1 row)


Alternatively, you can avoid this problem by casting all types to MultiPoint with ST_Multi().