UPSERT in a WHILE loop fails with invalid column reference

Question

I want to run an UPSERT in a WHILE loop . This is my query:

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 $$

But when I run this query, it gives error:

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.

How to fix it?

Erwin Brandstetter · Answer 1 · 2021-03-02T04:55:23.283

The immediate cause for the error is that you are trying to reference input column names, but only target column names are visible in the UPDATE part of the UPSERT. And you have to table-qualify those with the virtual table name EXCLUDED.

But there is more. Use a set-based solution based on generate_series() instead of the loop in a DO command. Much more efficient:

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: Consider a single column of type date to replace the three columns date_year, date_month, date_day. Much cleaner and more efficient.

UPSERT in a WHILE loop fails with invalid column reference

1 Answers1