How do I uniquely update values of a text column before applying UNIQUE constraint?

Question

I need to apply a unique constraint to a TEXT field which may or may not already contain duplicate values. If for instance I have ABC twice, I don't mind renaming them as ABC-1, ABC-2, but what if I already had ABC-1 as an entry? Seems like a bit of an recursive problem. I probably need to do this through a cursor, selecting all duplicate rows and updating them one by one.

Does anyone have a snippet to share? I'm using PostgreSQL 9.4 and plpgsql.

Answer 1

Ended up writing a nested loop which will select all duplicate entries in table dup_table and add a dash to each duplicate dup_name field until no duplicate records exist.

DO $$DECLARE 
  tmpRec RECORD; tmpInt INTEGER;
BEGIN
    LOOP
      tmpInt := 0;    

      FOR tmpRec IN 
        SELECT x_id FROM (
          SELECT x_id, ROW_NUMBER()
          OVER (PARTITION BY dup_name) AS "dup"
          FROM dup_table
        ) x WHERE "dup" > 1 
      LOOP
        tmpInt := 1;

        UPDATE dup_table SET dup_name = dup_name || '-' WHERE x_id = tmpRec.x_id;

      END LOOP;

      EXIT WHEN tmpInt = 0;
    END LOOP;
END$$;