Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types?

Question

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.

Answer 1

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) <> '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().

Answer 2

Workaround (because I don't consider myself this to be "an answer")

You can have one function loop through the result of the select you try to insert into the final table, and inform you before inserting the value. If one fails, you know which one it is:

You create one function:

CREATE OR REPLACE FUNCTION insert_one_by_one (_sql_select_txt text) RETURNS void AS
$$
DECLARE
    _r t%rowtype ;
BEGIN
    FOR _r IN EXECUTE _sql_select_txt LOOP
        RAISE NOTICE 'Going to insert: id=%, p=%', _r.id, ST_AsText(_r.p) ;
        INSERT INTO t VALUES (_r.*) ;
    END LOOP ;
END ;
$$
LANGUAGE plpgsql ;

And you call it by passing the text of a (well formed) SQL statement returning a table with the same type as t:

SELECT insert_one_by_one
($$
  SELECT * FROM
  (
  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)')    )
  ) AS v (id, p)
$$) ;

This will produce the following output (on pgAdmin III Message Window, or on stdout/stderr [I think] if you use psql):

NOTICE:  Going to insert: id=1, p=POINT(0 0)
CONTEXT:  PL/pgSQL function insert_one_by_one(text) line 6 at RAISE
NOTICE:  Going to insert: id=2, p=POINT(1 2)
CONTEXT:  PL/pgSQL function insert_one_by_one(text) line 6 at RAISE

ERROR:  Geometry type (MultiPoint) does not match column type (Point)
CONTEXT:  PL/pgSQL function insert_one_by_one(text) line 5 at FOR over EXECUTE statement

********** Error **********

ERROR: Geometry type (MultiPoint) does not match column type (Point)
SQL state: 22023
Context: PL/pgSQL function insert_one_by_one(text) line 5 at FOR over EXECUTE statement

---

Warning: this will severely slow down your queries. IMHO, should only be used as a "post-facto" debugging tool when your original INSERT query fails.

---

Strangely enough, I have not been able to make a function write a message only when an error appears by catching it. The original intention was to use this function:

CREATE OR REPLACE FUNCTION insert_one_by_one (_sql_txt text) RETURNS void AS
$$
DECLARE
    _r t%rowtype ;
BEGIN
    FOR _r IN EXECUTE _sql_txt LOOP
        BEGIN
            INSERT INTO t VALUES (_r.*) ;
        EXCEPTION 
            WHEN SQLSTATE '22023' THEN
                RAISE EXCEPTION  'Error while trying to insert id = %, p = %', _r.id, _r.p USING ERRCODE = '22023';
            WHEN others THEN
                RAISE EXCEPTION  'What ??' ;
        END ;
    END LOOP ;
END ;
$$
LANGUAGE plpgsql ;

... but with, it you just get:

********** Error **********

ERROR: Geometry type (MultiPoint) does not match column type (Point)
SQL state: 22023
Context: PL/pgSQL function insert_one_by_one(text) line 5 at FOR over EXECUTE statement

I guess, this means that PostGIS hacks quite deep down within PostgreSQL.