Day to day we have to delete duplicate data from the database.
An upgrade requires Primary key where one didn’t exist before or a db let it guard down and didn’t enforce the PK’s (yeah looking at you postgres!)
Or if an upgrade can’t create a new pk then this is useful. The SQL Server commands to find this are there too.
In the example below we’re looking at the table VPX_TYPE_MAP. The primary key of this table has the following columns. type_name, array_num, mo_ref
An upgrade of VC was trying to create a primary key with these columns but it failed as duplicate rows existed.
WITH CTE AS(
SELECT TYPE_NAME, ARRAY_NUM, MO_REF,
RN = ROW_NUMBER()OVER(PARTITION BY TYPE_NAME ORDER BY TYPE_NAME)
delete FROM CTE WHERE RN > 1
DELETE FROM vpx_type_map
WHERE ctid IN (SELECT ctid
ROW_NUMBER() OVER (partition BY type_name, array_num, mo_ref ORDER BY type_name) AS rnum
FROM vpx_type_map) t
WHERE t.rnum > 1);
where rowid in
(select rowid from
(partition by type_name, array_num, mo_ref ORDER BY type_name) dup
where dup > 1);