<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><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: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;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle21
{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]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The thing about CTEs is that in PostgreSQL 12+ they don’t necessarily materialize. So the behavior should be the same CTE / Subquery (unless you throw in an OFFSET 0 which will absolutely force a materialization). You can do that in subquery as well.<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'>AS mentioned in my other note – ST_Area, ST_Perimeter are very low cost functions so I would expect no materialization for PG 12+ regardless if you use CTE or subquery.<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 [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Marco Boeringa<br><b>Sent:</b> Monday, September 28, 2020 3:37 AM<br><b>To:</b> postgis-users@lists.osgeo.org<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>Regina,<o:p></o:p></p><p>Thanks for your suggestion.<o:p></o:p></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?<o:p></o:p></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:<o:p></o:p></p><p>UPDATE <MY_TABLE> SET area = ST_Area(<GEOMETRY_COLUMN>), area_perimeter = ST_Area(<GEOMETRY_COLUMN>) / ST_Perimeter(<GEOMETRY_COLUMN>)<o:p></o:p></p><p>in your code, instead of more elaborate construct involving a join.<o:p></o:p></p><p>Marco<o:p></o:p></p><div><p class=MsoNormal>Op 28-9-2020 om 03:26 schreef Regina Obe:<o:p></o:p></p></div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><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.</span><o:p></o:p></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So something like</span><o:p></o:p></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></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 </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></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 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 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</span><o:p></o:p></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">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">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">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-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><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">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">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">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-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><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 size=2 width="100%" 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">postgis-users@lists.osgeo.org</a><o:p></o:p></pre><pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre></blockquote></div><p class=MsoNormal><br><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">postgis-users@lists.osgeo.org</a><o:p></o:p></pre><pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre></blockquote><pre style='text-align:center'><hr size=2 width="100%" 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">postgis-users@lists.osgeo.org</a><o:p></o:p></pre><pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre></blockquote></div><p class=MsoNormal><br><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">postgis-users@lists.osgeo.org</a><o:p></o:p></pre><pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users">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">postgis-users@lists.osgeo.org</a><o:p></o:p></pre><pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre></blockquote></div></div></body></html>