ST_Centroid in view produces geometry without further definition from geometry(polygon, ssid)

Question

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?

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;