<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Regina,</p>
<p>Thanks for your suggestion.<br>
</p>
<p>How is this performance wise? Is not using a CTE as in your
suggestion, supposedly faster than with using a CTE, or is this
just a syntax thing and performance is expected to be equal?</p>
<p>It would still be nice though, if PostgreSQL somehow handled this
automatically, and one could use the most basic form yet be sure
it was optimized. It also reads more easily to just see:</p>
<p>UPDATE <MY_TABLE> SET area =
ST_Area(<GEOMETRY_COLUMN>), area_perimeter =
ST_Area(<GEOMETRY_COLUMN>) /
ST_Perimeter(<GEOMETRY_COLUMN>)</p>
<p>in your code, instead of more elaborate construct involving a
join.<br>
</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 28-9-2020 om 03:26 schreef Regina
Obe:<br>
</div>
<blockquote type="cite"
cite="mid:000001d69536$60e8a400$22b9ec00$@pcorp.us">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]-->
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Consolas",serif;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">I
prefer doing it in the FROM and not bothering using a CTE.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">So
something like<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<pre>UPDATE <MY_TABLE> SET area = f.area, area_perimeter = f.area/f.perimeter<o:p></o:p></pre>
<pre>FROM (SELECT id, ST_Area(<GEOMETRY_COLUMN>) AS area, ST_Perimeter(<GEOMETRY COLUMN>) AS perimeter<o:p></o:p></pre>
<pre> FROM <MY TABLE> ) AS f<o:p></o:p></pre>
<pre>WHERE f.id = <MY TABLE>.id;<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre><o:p> </o:p></pre>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Note
the FROM does not need to be the same as your table, you
just need to have a common join. That makes it particularly
handy for updating with aggregate values <o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<div style="border:none;border-left:solid blue 1.5pt;padding:0in
0in 0in 4.0pt">
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif">
postgis-users
[<a class="moz-txt-link-freetext" href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On
Behalf Of </b>Marco Boeringa<br>
<b>Sent:</b> Sunday, September 27, 2020 1:18 PM<br>
<b>To:</b> <a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<b>Subject:</b> Re: [postgis-users] Setting multiple
columns in one UPDATE request<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p>Thanks for pointing that out Alexander. I was just about to
start a test after some code modifications, but now realized
based on the example you pointed out that I was missing the
"FROM cte" clause in my SQL statement. It feels a bit
unnatural to have to specify that one, as you already define
the cte name after the WITH keyword. But this example sorted
it out.<o:p></o:p></p>
<p>Curious to see how it runs and if it leads to a measurable
performance enhancement.<o:p></o:p></p>
<p>Marco<o:p></o:p></p>
<div>
<p class="MsoNormal">Op 27-9-2020 om 18:30 schreef Alexander
Gataric:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">You have
the correct link. Here's an example.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><a
href="https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql"
moz-do-not-send="true">https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql</a><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Get <a
href="http://www.bluemail.me/r?b=16117"
moz-do-not-send="true">BlueMail for Android</a> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">On Sep 27, 2020, at 7:47 AM, Marco
Boeringa <<a href="mailto:marco@boeringa.demon.nl"
target="_blank" moz-do-not-send="true">marco@boeringa.demon.nl</a>>
wrote: <o:p></o:p></p>
<blockquote style="border:none;border-left:solid #CCCCCC
1.0pt;padding:0in 0in 0in
6.0pt;margin-left:4.8pt;margin-right:0in">
<p>Hi Alexander, <o:p></o:p></p>
<p>I guess I could use the optional WITH clause that is
part of UPDATE in PostgreSQL?:<o:p></o:p></p>
<div>
<p class="MsoNormal"><a
href="https://www.postgresql.org/docs/12/sql-update.html"
moz-do-not-send="true">https://www.postgresql.org/docs/12/sql-update.html</a><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">It would have been nice to see an
example of such usage in the PostgreSQL help, but
I'll figure it out.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Marco<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Op 27-9-2020 om 13:59 schreef
Alexander Gataric:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">Rewrite
to have a CTE with the area calculations and join
to the table.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Get <a
href="http://www.bluemail.me/r?b=16117"
moz-do-not-send="true">BlueMail for Android</a>
<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">On Sep 27, 2020, at 2:29 AM,
Marco Boeringa <<a
href="mailto:marco@boeringa.demon.nl"
target="_blank" moz-do-not-send="true">marco@boeringa.demon.nl</a>>
wrote: <o:p></o:p></p>
<blockquote style="border:none;border-left:solid
#CCCCCC 1.0pt;padding:0in 0in 0in
6.0pt;margin-left:4.8pt;margin-right:0in">
<pre>Hi all,<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>This may be an irrelevant basic question, but I just cannot find a clear <o:p></o:p></pre>
<pre>answer to this, there is no documentation in the PostgreSQL docs for <o:p></o:p></pre>
<pre>UPDATE about this:<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>In case I set multiple columns using some PostGIS function that clearly <o:p></o:p></pre>
<pre>has a (considerable) cost associated with it, and the value of the first <o:p></o:p></pre>
<pre>column being set is also needed to set the second column, does <o:p></o:p></pre>
<pre>PostgreSQL automatically optimize this and re-use the value already <o:p></o:p></pre>
<pre>calculated for column 1 to set column 2's value, or is each column's SET <o:p></o:p></pre>
<pre>statement treated as independent entity?<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>E.g. let's say I want to calculate both area, and the division of area <o:p></o:p></pre>
<pre>and perimeter, like:<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>UPDATE <MY_TABLE> SET area = ST_Area(<GEOMETRY_COLUMN>), area_perimeter <o:p></o:p></pre>
<pre>= ST_Area(<GEOMETRY_COLUMN>) / ST_Perimeter(<GEOMETRY_COLUMN>)<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>Will PostgreSQL only process 'ST_Area(<GEOMETRY_COLUMN>)' once in this <o:p></o:p></pre>
<pre>case, thus saving CPU load, or do I need to rewrite the SQL statement <o:p></o:p></pre>
<pre>somehow to achieve that?<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>Marco<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre style="text-align:center"><hr width="100%" size="2" align="center"></pre>
<pre><o:p> </o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a href="mailto:postgis-users@lists.osgeo.org" moz-do-not-send="true">postgis-users@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
</blockquote>
</div>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a href="mailto:postgis-users@lists.osgeo.org" moz-do-not-send="true">postgis-users@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
</blockquote>
<pre style="text-align:center"><hr width="100%" size="2" align="center"></pre>
<pre><o:p> </o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a href="mailto:postgis-users@lists.osgeo.org" moz-do-not-send="true">postgis-users@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
</blockquote>
</div>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a href="mailto:postgis-users@lists.osgeo.org" moz-do-not-send="true">postgis-users@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
</blockquote>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
</body>
</html>