在冲突中使用postgis函数做更新

Question

我想在现有的表中插入数据: sentinel_2

表"sentinel_2“有一个名为"sentinel_2_pkey”的唯一约束，该约束设置为(x_sw、y_sw、sensing_date)的组合是唯一的。

插入时，我希望使用该函数更新字段"ras1“和"ras2”(数据类型栅格)

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

ras1和ras2分别称为b02_b03_b04_b08_msk和b05_b06_b07_b8a_b11_b12_msk。

将被应用。

目标表的其他列或多或少是无关的/稍后将查看应该存储哪些值。

这是我到目前为止尝试过的，但是由于选择部分的原因，我无法将函数保存为触发器函数。

加入：

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);

直接使用该功能：

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);

使用另一个子查询解决方案尝试了它：

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);

而另一次尝试，这一次，我得到错误代码“关系”“排除”在python中，当试图插入数据。

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);

Answer 1

所以很明显，这个问题是因为我在外部表上使用了INSERT而产生的。我直接在另一个数据库中的表上使用了这个解决方案，它起了作用。

    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);