时间戳

首页 > 解决方案 > WHILE 循环中的 UPSERT 因列引用无效而失败

sql - WHILE 循环中的 UPSERT 因列引用无效而失败

问题描述

我想在WHILE循环中运行 UPSERT。这是我的查询：

do $$ declare start_date date := '2021-01-16';
begin 
    while start_date::date < '2021-03-01' loop
    insert
        into
        A ( id,
        "year",
        "month",
        movement,
        status)
        (
        select
            id,
            date_year,
            date_month,
            s_movement,
            move_status
        from
            B
        where
            date_year = extract(year
        from
            start_date::date)
            and date_month = (extract(month
        from
            start_date::date)+ 1)
            and date_day = extract(day
        from
            start_date::date))
       on conflict (id) do 
       update set status = move_status, movement = s_movement;
   start_date:=start_date+interval '1 day';
   end loop;
end $$

但是当我运行这个查询时，它给出了错误：

SQL Error [42703]: ERROR: column "move_status" does not exist Hint:
There is a column named "move_status" in table "*SELECT*", but it cannot be referenced from this part of the query.

如何解决？

标签： sqlpostgresqlplpgsqlupsertgenerate-series

解决方案

错误的直接原因是您试图引用输入列名，但UPDATE在 UPSERT 部分中只有目标列名可见。而且您必须使用虚拟表名EXCLUDED对那些进行表限定。

但还有更多。使用基于集合的解决方案，generate_series()而不是命令中的循环DO。效率更高：

INSERT INTO A
      (id, year     , month     , movement  , status)
SELECT id, date_year, date_month, s_movement, move_status
FROM   generate_series(timestamp '2021-01-16'
                     , timestamp '2021-02-28'
                     , interval '1 day')  start_date
JOIN   B ON date_year  = extract(year  FROM start_date)
        AND date_month = extract(month FROM start_date) + 1
        AND date_day   = extract(day   FROM start_date)
ON     CONFLICT (id) DO UPDATE
SET    status   = EXCLUDED.status                     -- here!
     , movement = EXCLUDED.movement;                  -- and here!

旁白：考虑一个类型的单列date来替换三列date_year, date_month, date_day。更清洁，更高效。

进一步阅读：

推荐阅读