Postgis allows to add invalid geometry

Question

I have a table layer with a geometry column geom. This geometry column has such constraints:

 CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
 CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text),
 CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 3857)

So, as you can see, there is nothing special about this geometry column. But what is interesting, is that it allows me to add incorrect geometry to the table:

insert into layer (geom) values (
    ST_GeomFromText('POLYGON((4831087.7172221 7576170.7140277,
      4829023.9174584 7556144.212617, 
      4834450.9464667 7556602.8347867, 
      4833533.7021273 7575329.9067165, 
      4831087.7172221 7576170.7140277),
     (4815647.4375453 7566616.0854925, 
      4817864.1113651 7574183.3512927, 
      4825049.1920219 7573342.5439816, 
      4821609.5257499 7567609.7668602, 
      4815647.4375453 7566616.0854925))', 3857)
);

The thing is this is not a polygon with a "hole". These are two different polygons which stay apart. Why is that? Why does Postgis allow to add incorrect geometry?

EDIT

I checked this geometry with ST_IsValid and it is false. So, it all looks like a bug in Postgis.

This is by design, and not a bug. Invalid geometries exist and must be stored somehow and ideally fixed. If you don't want them in a table, add a check constraint (as I see you have done in a comment). — Mike T

Laurenz Albe Laurenz Albe · Accepted Answer · 2016-07-12T08:59:48

You can create a polygon from any closed line, like

ST_GeomFromText('POLYGON((0 0, 0 1, 1 0, 1 1, 0 0))')

even though that does not make a valid polygon.

Use a check constraint with the st_isvalid() function!

Postgis allows to add invalid geometry

1 Answers