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.


1 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.

  tmpRec RECORD; tmpInt INTEGER;
      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 
        tmpInt := 1;

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

      END LOOP;

      EXIT WHEN tmpInt = 0;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.