Update columns' values in CTE-CodePudding

I have three CTEs and I want to update two columns in the last CTE, but I get an error that "the relation does not exist". I am using the CTEs, because, I want to store the whole process in a materialised view future use. I know there are many examples on the internet and several questions here, but I could not get it to work.

CREATE MATERIALIZED VIEW collection.issue1 AS (
    WITH Buffer_table AS 
    (
        SELECT id, geom, ST_buffer(ST_transform(geom,2952),20) as buffer_geom 
        From locations
    )
, 
 locations_events AS 
    (
        SELECT A.id, event_id, date, field3, field4, field5, field6, field7,field8, field9
        From events.safe_copy
        CROSS JOIN LATERAL 
        (
            Select id
            FROM Buffer_table  
            WHERE ST_Within(ST_Transform(ST_SetSRID(ST_MakePoint("LONGITUDE", "LATITUDE"), 4326), 2952), buffer_geom) 
            AND date >= '2015-01-01' AND  date <='2020-12-31'
            AND field6 in ('2') 
            AND field5 in ('2')
            AND field7 in ('3', '5') 
            AND field3 in ('1','2') 
        ) a 
    )
    UPDATE locations_events SET
    field8 = CASE WHEN field8 IS NULL THEN 'No' ELSE 'Yes' END,
    field9 = CASE WHEN field9 in ('3','4') THEN 'Yes' ELSE 'No' END;

CodePudding user response：

As @smvenk mentioned, cte and mat views aren't updatable. You could move the case when to the select statement in the locations_events cte :) For example:

CREATE MATERIALIZED VIEW collection.issue1 AS (
WITH Buffer_table AS 
    (
        SELECT id, geom, ST_buffer(ST_transform(geom,2952),20) as buffer_geom 
        From locations
    )

SELECT  A.id, 
        event_id, 
        date, 
        field3, 
        field4, 
        field5, 
        field6, 
        field7,
        CASE WHEN field8 IS NULL THEN 'No' ELSE 'Yes' END as field8, 
        CASE WHEN field9 in ('3','4') THEN 'Yes' ELSE 'No' END as field9

From events.safe_copy

        CROSS JOIN LATERAL 
        (
            Select id
            FROM Buffer_table  
            WHERE ST_Within(ST_Transform(ST_SetSRID(ST_MakePoint("LONGITUDE", "LATITUDE"), 4326), 2952), buffer_geom) 
            AND date >= '2015-01-01' AND  date <='2020-12-31'
            AND field6 in ('2') 
            AND field5 in ('2')
            AND field7 in ('3', '5') 
            AND field3 in ('1','2') 
        ) a

CodePudding user response：

I hope this one is a good starting point for UPDATE with JOIN and ST_DWITHIN().

Also you can save the query in a stored procedure.

Attention: This query does not use spatial indexes, that is, it will be slowly on a large number of points

UPDATE events.safe_copy sc
   SET field8 = CASE WHEN field8 IS NULL THEN 'No' ELSE 'Yes' END
     , field9 = CASE WHEN field9 in ('3','4') THEN 'Yes' ELSE 'No' END
  FROM locations loc
 WHERE ST_DWithin ( loc.geom -- geom in EPSG:4326
                  , ST_SetSRID
                             ( ST_MakePoint("LONGITUDE", "LATITUDE")
                             , 4326
                             ) -- geom in EPSG:4326
                  , 20 -- distance in meters
                  , true -- use_spheroid
                  ) 
   and sc.date >= '2015-01-01'
   and sc.date <='2020-12-31'
   and sc.field6 = '2'
   and sc.field5 = '2'
   and sc.field7 in ('3', '5') 
   and sc.field3 in ('1','2')