Use postgis function on CONFLICT DO UPDATE

I want to insert Data in an existing table: sentinel_2

The table "sentinel_2" has an unique constraint called "sentinel_2_pkey" which is set that the combination of (x_sw, y_sw, sensing_date) is unique.

When inserting I want the fields "ras1" and "ras2" (datatype raster) to be updated using the function

ST_Union([sentinel_2.ras1, excluded.ras1],"MAX"]) and the same for ras2

ras1 and ras2 are called b02_b03_b04_b08_msk and b05_b06_b07_b8a_b11_b12_msk respectively

to be applied.

The other columns of the target-table are more or less irrelevant/will be looked upon later which of the values should be stored.

This is what I tried so far but I can not save the function as a trigger function due to the SELECT-part

Join:

ON CONFLICT ON CONSTRAINT sentinel_2_pkey 
            DO 
                UPDATE SET
                (x_sw, y_sw, sensing_date, ras1, ras2, coregistered,s2_sensor,s2_level,cloud_cover) = 
                (excluded.x_sw, excluded.y_sw,excluded.sensing_date,
                SELECT ST_UNION(ras1,'MAX') FROM sentinel_2 INNER JOIN EXCLUDED ON sentinel_2.x_sw = excluded.x_sw AND sentinel_2.y_sw = excluded.y_sw AND sentinel_2.sensing_date = excluded.sensing_date, 
                SELECT ST_UNION(ras2, 'MAX') FROM sentinel_2 INNER JOIN EXCLUDED ON sentinel_2.x_sw = excluded.x_sw AND sentinel_2.y_sw = excluded.y_sw AND sentinel_2.sensing_date = excluded.sensing_date, 
                excluded.coregistered, 
                excluded.s2_sensor,
                excluded.s2_level, 
                excluded.cloud_cover);

Using the function directly:

UPDATE SET
                (x_sw, y_sw, sensing_date, ras1, ras2, coregistered,s2_sensor,s2_level,cloud_cover) = 
                (excluded.x_sw, excluded.y_sw,excluded.sensing_date,
                ST_UNION([sentinel_2.ras1, excluded.ras1],'MAX'), 
                 ST_UNION([sentinel_2.ras2, excluded.ras2],'MAX'), 
                excluded.coregistered, 
                excluded.s2_sensor,
                excluded.s2_level, 
                excluded.cloud_cover);

Tried it with another subquery-solution:

DO 
                UPDATE SET 
                (x_sw, y_sw, sensing_date, b02_b03_b04_b08_msk, b05_b06_b07_b8a_b11_b12_msk, coregistered,s2_sensor,s2_level,cloud_cover) = 
                (excluded.x_sw, excluded.y_sw,excluded.sensing_date,
                (select st_union(tssetof.b02_b03_b04_b08_msk::raster, 'MAX'::text) from (select sentinel_2.b02_b03_b04_b08_msk union select excluded.b02_b03_b04_b08_msk) as tssetof) , 
                (SELECT st_union(tssetof2.b05_b06_b07_b8a_b11_b12_msk::raster, 'MAX'::text) from (select sentinel_2.b05_b06_b07_b8a_b11_b12_msk union select excluded.b05_b06_b07_b8a_b11_b12_msk) as tssetof2),
                excluded.coregistered, 
                excluded.s2_sensor,
                excluded.s2_level, 
                excluded.cloud_cover);

And another try, this time I get the error code "relation "excluded" does not exist" in python when trying to insert data.

ON CONFLICT ON CONSTRAINT sentinel_2_pkey DO 
            UPDATE SET 
            (x_sw, y_sw, sensing_date, b02_b03_b04_b08_msk, b05_b06_b07_b8a_b11_b12_msk, coregistered,s2_sensor,s2_level,cloud_cover) = 
            (EXCLUDED.x_sw, EXCLUDED.y_sw,EXCLUDED.sensing_date,
            (SELECT ST_UNION(b02_b03_b04_b08_msk,'MAX') FROM public.sentinel_2 INNER JOIN EXCLUDED ON (public.sentinel_2.x_sw = EXCLUDED.x_sw AND public.sentinel_2.y_sw = EXCLUDED.y_sw AND public.sentinel_2.sensing_date = EXCLUDED.sensing_date)) , 
            (SELECT ST_UNION(b02_b03_b04_b08_msk, 'MAX') FROM public.sentinel_2 INNER JOIN EXCLUDED ON (public.sentinel_2.x_sw = EXCLUDED.x_sw AND public.sentinel_2.y_sw = EXCLUDED.y_sw AND public.sentinel_2.sensing_date = EXCLUDED.sensing_date)),
            EXCLUDED.coregistered, 
            EXCLUDED.s2_sensor,
            EXCLUDED.s2_level, 
            EXCLUDED.cloud_cover);

Solution

So apparently the problem did occur due to me using the INSERT on a foreign table. I used thhis solution directly on the table in another database and it worked

    UPDATE SET 
            (x_sw, y_sw, sensing_date, b02_b03_b04_b08_msk, b05_b06_b07_b8a_b11_b12_msk, coregistered,s2_sensor,s2_level,cloud_cover) = 
            (excluded.x_sw, excluded.y_sw,excluded.sensing_date,
            (select st_union(tssetof.b02_b03_b04_b08_msk::raster, 'MAX'::text) from (select sentinel_2.b02_b03_b04_b08_msk union select excluded.b02_b03_b04_b08_msk) as tssetof) , 
            (SELECT st_union(tssetof2.b05_b06_b07_b8a_b11_b12_msk::raster, 'MAX'::text) from (select sentinel_2.b05_b06_b07_b8a_b11_b12_msk union select excluded.b05_b06_b07_b8a_b11_b12_msk) as tssetof2),
            excluded.coregistered, 
            excluded.s2_sensor,
            excluded.s2_level, 
            excluded.cloud_cover);