Upsert with parallel unnest raises "column does not exist" in UPDATE part

Question

I have a parallel unnest that requires an upsert:

CREATE FUNCTION public.sort_category(category_ids integer[], sort integer[])
RETURNS void
AS $$
  INSERT INTO upsert (user_id, category_id, sort)
    SELECT sessions.user_id, category_id, sort_index
    FROM  UNNEST($1, $2) as input(category_id, sort_index), sessions
    WHERE sessions.session_token = 'a'
    ON CONFLICT (user_id, category_id) 
    DO 
      UPDATE SET sort = sort_index;
$$ LANGUAGE sql VOLATILE STRICT;

From my command line, I receive this error:

ERROR: column "sort_index" does not exist LINE 11: UPDATE SET sort = sort_index;

HINT: There is a column named "sort_index" in table "SELECT", but it cannot be referenced from this part of the query.

Fiddle is here, please. Error is different on fiddle, but function is same:

https://www.db-fiddle.com/f/xnUGCeonxPNEnaSikazka/0

Erwin Brandstetter · Accepted Answer · 2021-09-16T01:06:44.473

You must use the special table EXCLUDED in the UPDATE part of an UPSERT.

CREATE FUNCTION public.sort_category(category_ids integer[], sort integer[])
  RETURNS void
  LANGUAGE sql VOLATILE STRICT AS
$func$
INSERT INTO upsert (user_id, category_id, sort)
SELECT s.user_id, input.category_id, input.sort_index
FROM   unnest($1, $2) AS input(category_id, sort_index)
CROSS  JOIN sessions s
WHERE  s.session_token = 'a'
ON CONFLICT (user_id, category_id) DO UPDATE
SET    sort = EXCLUDED.sort;                      --  here!
$func$

db<>fiddle here

See:

How to UPSERT multiple rows with individual values in one statement?

Upsert with parallel unnest raises "column does not exist" in UPDATE part

1 Answers1