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
CodePudding user response:
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: