-- consider using a trigger here to avoid writing no-op updates to disc
)
SELECT * FROM delete_these
UNION ALL
SELECT * FROM and_update_the_rest
;
I'd suggest you find some way to abstract away the table name and columns so you can write the logic once and specify the table name and columns as part of the input.
OR
WITH going_away_author_change AS (
DELETE FROM tbl
RETURNING uid, author
),
WITH going_away_user_change AS (
DELETE FROM tbl
RETURNING uid, author
)
INSERT INTO tbl
SELECT uid, new_author FROM going_away_author_change
UNION ALL
SELECT new_uid, author FROM going_away_user_change
ON CONFLICT DO NOTHING -- haven't used personally, you should get the idea though
OR
WITH nuclear_option AS (
DELETE FROM tbl
RETURNING *
), make_new_records AS (
SELECT DISTINCT ON (uid, author) uid, author, ... -- ensure this outputs records meeting your PK constraint
FROM nuclear_option -- sprinkle renaming logic where needed