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
INSERThave 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?
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.