Inserting multiple rows into a table with a single INSERT INTO statement fails (as expected) when some of these rows have values of a PostGIS geometry type incompatible to the respective row's PostGIS geometry type in the destination table:

CREATE EXTENSION postgis;

CREATE TABLE t (
    id integer,
    p geometry(POINT)
);

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)')    );

The error message tell's exactly what's wrong:

ERROR:  Geometry type (MultiPoint) does not match column type (Point)

But it lacks useful context information like:

• How many rows of the INSERT have this problem?
• What were the exact values?
• What values were in the other columns of the offending rows? (In the example above: What were the IDs of the offending rows?)

Can I adapt the INSERT statement so that PostgreSQL would give me this information, e.g. include the complete content of the offending rows in the error message, like it does for violated NOT NULL constraints?

E.g.

CREATE TABLE s (
   i integer NOT NULL, t text
);

INSERT INTO s
VALUES
    (1   , 'foo'),
    (NULL, 'bar'),
    (2   , 'baz');

results in a much more useful message:

ERROR:  null value in column "i" violates not-null constraint
DETAIL:  Failing row contains (null, bar).

Motivation / Use-Case

When you can just look at the VALUES listed in the INSERT statement and see the offending rows, this is of course not that relevant. But the same issue arises when the inserted rows are selected from another table or computed dynamically, and then a more informative error message would indeed be useful.

DROP TABLE if exists osmaxx.building_a;
CREATE TABLE osmaxx.building_a (
    osm_id bigint,
    lastchange timestamp without time zone,
    geomtype char(1),
    geom geometry(MULTIPOLYGON, 4326),
    type text,
    name text,
    name_en text,
    name_fr text,
    name_es text,
    name_de text,
    int_name text,
    label text,
    tags text,
    height float
);

INSERT INTO osmaxx.building_a
  SELECT osm_id as osm_id,
    osm_timestamp as lastchange ,
    CASE
     WHEN osm_id<0 THEN 'R' -- R=Relation
     ELSE 'W'               -- W=Way
     END AS geomtype,
    ST_Multi(way) AS geom,
    building as type,
    name as name,
    "name:en" as name_en,
    "name:fr" as name_fr,
    "name:es" as name_es,
    "name:de" as name_de,
    int_name as name_int,
    case
        when name is not null AND name = osml10n_translit(name) then name
        when "name:en" is not null then "name:en"
        when "name:fr" is not null then "name:fr"
        when "name:es" is not null then "name:es"
        when "name:de" is not null then "name:de"
        when int_name is not null then osml10n_translit(int_name)
        when name is not null then osml10n_translit(name)
        else NULL
    end as label,
    cast(tags as text) as tags,
    cast_to_float_null_if_failed(height) as height
  FROM osm_polygon
WHERE building is not null;

Here it would have been really useful to know the osm_id of to-be-inserted rows that could not be inserted due to

Geometry type (MultiLineString) does not match column type (MultiPolygon)