Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE upsert (
    user_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    sort INTEGER,
    collapse BOOLEAN,
    PRIMARY KEY(user_id, category_id)
);

CREATE TABLE sessions (
    user_id INTEGER NOT NULL,
    session_token TEXT
);

INSERT INTO sessions (user_id, session_token) VALUES
  (6, 'foo')
, (7, 'a')
, (8, 'a');
3 rows affected

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, category_id, 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;
$func$;
SELECT public.sort_category('{1,2,3}', '{11,22,33}');

TABLE upsert;


sort_category


null




user_id
category_id
sort
collapse


7
1
11
null

8
1
11
null

7
2
22
null

8
2
22
null

7
3
33
null

8
3
33
null


SELECT public.sort_category('{1,2}', '{66,77}');

TABLE upsert;


sort_category


null




user_id
category_id
sort
collapse


7
3
33
null

8
3
33
null

7
1
66
null

8
1
66
null

7
2
77
null

8
2
77
null

user_id	category_id	sort	collapse
7	1	11	null
8	1	11	null
7	2	22	null
8	2	22	null
7	3	33	null
8	3	33	null