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

[Regina Obe]

I prefer doing it in the FROM and not bothering using a CTE.

 

So something like

 

UPDATE <MY_TABLE> SET area = f.area, area_perimeter = f.area/f.perimeter
FROM (SELECT id, ST_Area(<GEOMETRY_COLUMN>) AS area, ST_Perimeter(<GEOMETRY COLUMN>) AS perimeter
        FROM <MY TABLE> ) AS f
WHERE f.id = <MY TABLE>.id;
 
 

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 

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Marco Boeringa
Sent: Sunday, September 27, 2020 1:18 PM
To: postgis-users at lists.osgeo.org
Subject: Re: [postgis-users] Setting multiple columns in one UPDATE request

 

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 <marco at boeringa.demon.nl <mailto:marco at boeringa.demon.nl> > 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 <marco at boeringa.demon.nl <mailto:marco at boeringa.demon.nl> > 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
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users





_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users



  _____  

 
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users





_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200927/03d83c81/attachment.html>