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".
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.
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) ;
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
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.
The above set up follows current best practices, and has the intended behaviour - except that it also checks the SRID of the
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) );
I could figure out this issue on my own. ST_LineString is case sensitive and must be typed exactly as it is.
CHECK(ST_GeometryType(geom) = 'ST_LineString'::text
And I guess that so are all of this type of constants. I still do not understand why it is not throwing an 'invalid geometry' error when typed with the right spelling but without the proper case.(ie. ST_Linestring)