On Tue, Mar 14, 2017 at 1:25 PM, Alexander Farber < alexander.far...@gmail.com> wrote:
> > But this might give me conflicts, because there might be such a PK > already... > > You need to remove the "might" and figure out which ones will and which will not. You can update the ones will not and delete the ones that do. Free-thinking: WITH figure_things_outs AS ( -- not exactly sure how this looks but I would probably try to construct the new relation with the updates in place -- and use something like row_number() OVER (PARTITION BY uid, author ORDER BY pk) to detect future duplicates ), delete_these AS ( DELETE FROM tbl USING figure_things_out WHERE tbl.pk = figure_things_out.pk AND figure_things_out.row_number > 1 ), and_update_the_rest AS ( UPDATE tbl WHERE EXISTS ( ... WHERE row_number = 1 ) -- 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 ORDER BY uid, author, pk ) INSERT INTO tbl SELECT * FROM make_new_records; David J.
