PostGIS – Check Constraint on Geometry Column Not Working as Expected

constraintpostgispostgresql

I am going through the QGIS Training Manual (16.5.2) and could not figure out why the streets table throws a violation check error as trying to update a geometry column. I checked a very similar question at violates check constraint but couldn't get any clue. I understand "GeometryType" has been upgraded to "ST_GEOMETRYTYPE".

enter image description here

Best Answer

Look closely at both your CHECK constraint definition and the function docs: ST_GeometryType returns the actual type (following the ST_ prefix) formatted in PascalCase, i.e. ST_LineString!

You'd want to recreate that CHECK like so:

ALTER TABLE streets
  DROP CONSTRAINT enforce_geometry_chk
;

ALTER TABLE streets
  ADD CONSTRAINT enforce_geometry_chk
    CHECK (ST_GeometryType(geom) = 'ST_LineString'::TEXT OR geom IS NULL)
;

However:

While you may get some insights in managing table CONTRAINTs following that tutorial, PostGIS type enforcing mechanisms have evolved significantly since it was written (which, apparently, was a looong time ago...), and by now the suggested table set-up violates best practices.

The two PostGIS specific types GEOMETRY and GEOGRAPHY allow for an explicit definition of the geometry type when used as column definition, as well as the SRID you want to enforce[¹] on your geometries, e.g. [²]:

CREATE TABLE streets (
  id     INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "name" VARCHAR(50),
  geom   GEOMETRY(LINESTRING, 4326)
);

While in the early versions of PostGIS this was indeed realized by adding CHECK contraints during table creation, it now is implemented via the PostgreSQL type modificator system - an addition to core data types that allows direct enforcement of dynamic, type specific restrictions (see e.g. VARCHAR(50))

The above set up follows current best practices, and has the intended behaviour - except that it also checks the SRID of the INSERTed/UPDATEted geometries. In your case, since you use the EWKT string representation with attached SRID, it will just work.

[¹]: enforce really means checked against: it will not do anything to solve issues - it just raises an error like yours

[²]: Note here that, except for "name" which is wrapped in ", all other identifiers and SQL keywords are case insensitive, so the above is equivalent to

create table streets (
  id     int generated by default as identity primary key,
  "name" varchar(50),
  geom   geometry(linestring, 4326)
);
Related Question