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.
Curious to see how it runs and if it leads to a measurable performance
enhancement.
Marco
Op 27-9-2020 om 18:30 schreef Alexander Gataric:
You have the correct link. Here's an example.
https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql
Get BlueMail for Android <http://www.bluemail.me/r?b=16117>
On Sep 27, 2020, at 7:47 AM, Marco Boeringa <[email protected]
<mailto:[email protected]>> wrote:
Hi Alexander,
I guess I could use the optional WITH clause that is part of
UPDATE in PostgreSQL?:
https://www.postgresql.org/docs/12/sql-update.html
It would have been nice to see an example of such usage in the
PostgreSQL help, but I'll figure it out.
Marco
Op 27-9-2020 om 13:59 schreef Alexander Gataric:
Rewrite to have a CTE with the area calculations and join to the
table.
Get BlueMail for Android <http://www.bluemail.me/r?b=16117>
On Sep 27, 2020, at 2:29 AM, Marco Boeringa
<[email protected] <mailto:[email protected]>> wrote:
Hi all,
This may be an irrelevant basic question, but I just cannot find a clear
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 first
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 SET
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
= ST_Area(<GEOMETRY_COLUMN>) / ST_Perimeter(<GEOMETRY_COLUMN>)
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?
Marco
------------------------------------------------------------------------
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
------------------------------------------------------------------------
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
