<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<STYLE type=text/css>P {
MARGIN: 0px
}
</STYLE>
<META name=GENERATOR content="MSHTML 9.00.8112.16450"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial>You definitely want to stick with constraints
if</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial>1) you restore to 1.5 databases a lot (1.5 won't understand a
typmod table structure)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial>2) you have triggers where the data might come in as say
LINESTRING and your trigger changes it to MULTILINESTRING to satisfy
column.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial>(types are checked before triggers run (where as constraint
checks are after) so typmod unfortunately does not work for this use
case)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial>3) some inheritance cases where the child
tables have different types / srid than the parent</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial>Leo and Regina,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=803464022-12112012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of </B>Shira
Bezalel<BR><B>Sent:</B> Monday, November 12, 2012 11:27 AM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> [postgis-users] Should we change existing
geometry columns totypmod instead of constraint-based?<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV
style="FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 12pt"><BR><SPAN
style="BACKGROUND-COLOR: rgb(255,255,255)">Hi All,</SPAN>
<DIV style="BACKGROUND-COLOR: rgb(255,255,255)"><BR>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?
<DIV><BR></DIV>
<DIV><SPAN style="FONT-SIZE: 12pt">Thanks,</SPAN></DIV>
<DIV>Shira </DIV></DIV></DIV></BODY></HTML>