我有一個并行的unnest，需要一個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'/span>
ON CONFLICT (user_id, category_id)
做法
UPDATE SET sort = sort_index;
$$ LANGUAGE sql VOLATILE STRICT;
從我的命令列中，我收到這樣的錯誤：
ERROR: column "sort_index" does not exist LINE 11: UPDATE SET sort = sort_index;
提示：在表"SELECT"中有一個名為 "sort_index "的列，但它不能被查詢的這一部分所參考。 這一部分的查詢。
請看Fiddle。
https://www.db-fiddle.com/f/xnUGCeonxPNEnaSikazka/0
uj5u.com熱心網友回復：
你必須在UPSERT的UPDATE部分使用特殊表EXCLUDED。
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'/span>
ON CONFLICT (user_id, category_id) DO UPDATE
SET sort = EXCLUDED.sort; -- here!
$func$
db<>fiddle here
見：
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/306827.html
標籤：