I run PostgreSQL 9.1 and PostGIS 2.0. I have a table that contains one geometry column.

But that column contains point, polygons lines, etc.

So I set it like so,

ALTER TABLE jake ADD COLUMN jake_geom geometry;

and then I setted some constraints to make it geometry enabled, but not limited on a single geometry type

  ADD CONSTRAINT enforce_dims_geom CHECK (st_ndims(jake_geom) = 2);

  ADD CONSTRAINT enforce_srid_geom CHECK (st_srid(jake_geom) = 900913);

Now, I want to reproject the geometries in that column to 4326.

According to this, for the transform to work, I need to set a geometry type.

(the TYPE geometry(MultiPolygon,2163) part)

but my column does not have certain geometry type. How do I proceed?

Or I can simply do

ALTER COLUMN geom USING ST_Transform(geom,2163);

avoiding to set a type?


First you need to drop the srid constraint you have on your table. Even with an unconstrained geometry type, you can have the srid check in the typmod column definition so no need to resort to constraint

ALTER TABLE jake DROP CONSTRAINT enforce_srid_geom;
   ALTER COLUMN jake_geom 
       geometry(Geometry, 2163) USING ST_Transform(jake_geom, 2163);
  • After the reprojection, do I have to set the CONSTRAINT again like ALTER TABLE jake ADD CONSTRAINT enforce_srid_geom CHECK (st_srid(jake_geom) = 4326); ? Thanks – slevin Oct 10 '15 at 21:38
  • 1
    No. the typmod geometry(Geometry, 2163) acts like an srid = 2163 constraint. You've probably seen something like geometry(POLYGON,2163). Using Geometry is a catchall for any geometry subtype. – LR1234567 Oct 11 '15 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.