[postgis-users] Should we change existing geometry columns totypmod instead of constraint-based?

[Paragon Corporation]

Generally we would say especially if you have big tables, not to bother
since 9.1 and below does a table rewrite we believe.  For 9.2 I think this
may be an operation that doesn't require a rewrite.  Moving forward for new
tables it depends.  Again it depends.
 
Main advantage is you can use ALTER TABLE commands and also if used in views
(unadorned with functions), the type and srid is picked up in
geometry_columns.
 
For constraint based ones, the base table will be registered correctly, byt
you''ll need to type cast in the view like geom::geometry(POLYGON,4326) As
geom for views to be correctly picked up in goemetry_columns.
 
You definitely want to stick with constraints if
1) you restore to 1.5 databases a lot (1.5 won't understand a typmod table
structure)
2) you have triggers where the data might come in as say LINESTRING and your
trigger changes it to MULTILINESTRING to satisfy column.
(types are checked before triggers run (where as constraint checks are
after) so typmod unfortunately does not work for this use case)
3) some inheritance cases where the child tables have different types / srid
than the parent
 
Leo and Regina,
http://www.postgis.us
 

  _____  

From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Shira Bezalel
Sent: Monday, November 12, 2012 11:27 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Should we change existing geometry columns totypmod
instead of constraint-based?



Hi All, 

We have recently upgraded our databases to use PostGIS 2.0.1 from 1.5. Is it
recommended that we recreate our tables with geometry columns so that they
use the typmod geometries instead of the constraint-based geometries? Is
there any advantage to this? 

Thanks,
Shira 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121112/00952630/attachment.html>