[postgis-users] Setting multiple columns in one UPDATE request

Alexander Gataric gataric at usa.net
Sun Sep 27 04:59:41 PDT 2020

Rewrite to have a CTE with the area calculations and join to the table.

⁣Get BlueMail for Android ​

On Sep 27, 2020, 2:29 AM, at 2:29 AM, Marco Boeringa <marco at boeringa.demon.nl> wrote:
>Hi all,
>This may be an irrelevant basic question, but I just cannot find a
>answer to this, there is no documentation in the PostgreSQL docs for 
>UPDATE about this:
>In case I set multiple columns using some PostGIS function that clearly
>has a (considerable) cost associated with it, and the value of the
>column being set is also needed to set the second column, does 
>PostgreSQL automatically optimize this and re-use the value already 
>calculated for column 1 to set column 2's value, or is each column's
>statement treated as independent entity?
>E.g. let's say I want to calculate both area, and the division of area 
>and perimeter, like:
>UPDATE <MY_TABLE> SET area = ST_Area(<GEOMETRY_COLUMN>), area_perimeter
>Will PostgreSQL only process 'ST_Area(<GEOMETRY_COLUMN>)' once in this 
>case, thus saving CPU load, or do I need to rewrite the SQL statement 
>somehow to achieve that?
>postgis-users mailing list
>postgis-users at lists.osgeo.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200927/21427eda/attachment.html>

More information about the postgis-users mailing list