PostGIS – Fix Geometry Insert Violating Check Constraint

geometrypostgis

I have imported a shapefile containing point layer into postgis, and the SRID i have chosen was 4326. Now while inserting the values into the table I am getting a problem. My intension is to create a circle around that point using ST_Buffer.

My Table structure is 

CREATE TABLE "ankCircle"
(
  gid serial NOT NULL,
  id numeric(10,0),
  geom geometry,
  CONSTRAINT "ankCircle_pkey" PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),
  CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "ankCircle" OWNER TO postgres;

Query I am using is

INSERT INTO "ankCircle"(gid, id, geom)
VALUES (1, 1, ST_Buffer(ST_GeomFromText('POINT(100 90)',4326),50, 'quad_segs=8'));

error got

ERROR:  new row for relation "ankCircle" violates check constraint "enforce_geotype_geom"

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

ERROR: new row for relation "ankCircle" violates check constraint "enforce_geotype_geom"
SQL state: 23514

Best Answer

You have a constraint that is preventing the buffer result into being inserted in the table. This the constraint code:

CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),

The constraint is checking that the geometry type is POINT or is NULL (while ST_Buffer is returning a different geometry type). If that is not intentional you should change that constraint or create a new column (say buffer_geom) with a different constraint or no constraint at all.

Related Question