title: Postgres add seq# to distinguish unique rows in the database
body: <p>In an existing application, I have a table which has no primary key, which (rarely) has duplicate rows in it. For integration with another system, I need to add a column to the table that makes these duplicate rows unique. The table is essentially:</p>
  
  <pre>
  +------+---------+--------+
  | txn# | detail# | amount |
  +------+---------+--------+
  </pre>
  
  <p>I could just use an incrementing seq# for every existing row, however, the application changes will be to take the existing 'almost key' (basically transaction# detail#), and increment the sequence number for this combination (so, e.g., I'll have two rows for txn# 1, detail# 1, and have seq# 1 for the first and seq#2 for the second, and if txn#513, detail# 44 has 3 duplicate rows, these would have seq# 1,2,3 appropriately. It would be ideal (from a support perspective), if the data pre- and post- application changes was similarly set out. Is there some easy way to create this column, or do I need to cycle through the rows manually, resetting the sequence used every time the txn# or detail# changes?</p>
  
  <p>edited to add: the app is deployed on both Postgresql 7.4 &amp; 8.1, and the solution needs to work for both versions.</p>