Append table in PostGIS - Geometry Violation

Question

In PostGIS I have imported a shapefile - some of this is Invalid according to PostGIS.

Following online help I have first split the invalid polygons into a new table:

CREATE TABLE s_invalid AS
SELECT * FROM sdata
WHERE NOT ST_IsValid(the_geom);

DELETE FROM sdata
WHERE NOT ST_IsValid(the_geom);

I then buffered the invalid polygons by 0 to sort out the issue:

UPDATE s_invalid
SET the_geom = ST_Buffer(the_geom,0);

SELECT count(*), ST_IsValid(the_geom)
FROM s_invalid
GROUP BY ST_IsValid;

All seems ok, now I want to append the cleaned-up polygons back into the original table and this is where I have an issue. I have tried:

INSERT INTO sdata
SELECT * FROM s_invalid;

But there seems to be a mismatch with the geometry type from the best I can see - I get the message:

ERROR:  new row for relation "sdata" violates check constraint "enforce_geotype_the_geom"

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

ERROR: new row for relation "sdata" violates check constraint "enforce_geotype_the_geom"
SQL state: 23514

How can I sort this... where is the issue? Is there an easier way to sort this out? Thanks, I really want to get to grips with this!

Answer 1

You have MULTIPOLYGON constraint in sdata table, but ST_Buffer creates some POLYGON objects. Try:

UPDATE s_invalid
SET the_geom = ST_Multi(ST_Buffer(the_geom,0));

(or, alternatively, change constraint, so it accepts also POLYGONS).

Answer 2

from my answer here.

try to alter your table for dropping constraint:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

than add your constraint again after chanaging table name...

ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE products ADD CONSTRAINT the_constraint_name;

i hope it helps you...