I'm a Postgresql novice.

In a postgresql 9.2 + PostGIS instance I have one table with a geometry(polygon, ssid) column. From this table I am trying to create a view using ST_Centroid like so:

CREATE OR REPLACE VIEW schema.polys_points AS 
   SELECT poly_table.name, 
          ST_Centroid(geom) as geom 
   FROM poly_table;

This produces a view with a geometry() column where I would like it defined as geometry(point, ssid) column. Is this possible?


1 Answer 1


Yes, this is possible but you can't do it after the view is created. Normally, the view would just inherit the geom constraints from the source table, but since you are changing the geom from polygon to point in your query, it won't automatically register. You just need to add typemods to your geom column within the view definition:

create or replace view schema.polys_points as 
select poly_table.name, ST_Centroid(geom)::geometry(POINT, SRID) as geom 
from poly_table;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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