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

Question

我想在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.

如何修复?

Answer 1

这个错误的直接原因是您正在尝试引用输入列名，但是在UPSERT的UPDATE部分中只有目标列名可见。并且必须用虚拟表名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!

Aside:考虑用一个date类型的列来代替date_year、date_month、date_day这三个列。更干净，更高效。

进一步阅读:

• Postgres UPSERT reuse column values from INSERT on UPDATE

• INSERT…UPDATE

• 在PostgreSQL中生成两个日期之间的时间序列